KHIZHNYAKOVЖүктемеден Excel-де есеп жасау: айлар, санаттар және рейтингтер бойынша жиынтық
Жүктемені қойдым — бәрі есептелді: жүктемеден әмбебап есеп — Excel үлгісі — Үлгіні ашу →
Бухгалтер, менеджер немесе бөлімше басшысы үнемі бір жұмысты қайталайды: бағдарламадан жүктеме алады, Excel-де ашады, содан кейін қолмен жиынтық кесте құрады. Келесі айда деректер жаңа, ал жұмыс қайтадан басынан басталады. Әсіресе баған атаулары өзгерсе немесе жолдар қосылса, бұрынғы формулалар бұзылады.
Мақалада кез келген жүктемені қайта қолдануға болатын жиынтыққа қалай айналдыру керектігін көрсетеміз: деректерді дайындау, айлар мен санаттар бойынша қорытынды, өткен кезеңмен салыстыру, рейтинг және деректердің дұрыстығын тексеру.
Жүктемені дайындау
Жүктеме кез келген бағдарламадан келуі мүмкін, бірақ жиынтыққа жарау үшін оған кемінде төрт баған керек:
- күні;
- санат (өнім тобы, шығыс бабы, бөлім);
- контрагент (клиент немесе жеткізуші);
- сан және сома.
Жүктемені «Деректер» парағына бірден қойыңыз да, келесі жүктемені дәл сол жерге ауыстырып қойып отырыңыз. Негізгі ереже: жүктеме парағына өзгеріс енгізбей, жиынтықты бөлек парақта құрыңыз.
Күндерді мәтін ретінде келген жағдайда түзету қажет: =DATEVALUE(A2). Сандар да кейде мәтін болып келеді («1 250,50» бос орынмен). Ондайда =VALUE(SUBSTITUTE(E2;" ";"")) көмектеседі.
Ай мен жыл бағандарын қосыңыз
Жиынтық үшін екі көмекші баған қажет:
- ай:
=MONTH(A2) - жыл:
=YEAR(A2)
Одан кейін барлық есептеулер осы бағандарға сүйенеді. Мысалы, санаттың ай бойынша түсімі:
=SUMIFS($E:$E;$B:$B;"Тауар А";$G:$G;1;$H:$H;2025)
Мұндағы E — сома, B — санат, G — ай, H — жыл. Формуланы көшіргенде шарт ұяшыққа сілтеме жасаса, барлық санаттарға бір формула жетеді.
Айлар бойынша қорытынды
Он екі ай — он екі жол, ал бағандарда: сома, сан, өсім. Мысал:
| Ай | Сома, ₸ | Алдыңғы айға өсім | Өткен жылдың осы айына өсім |
|---|---|---|---|
| Қаңтар | 1 200 000 | 10% | |
| Ақпан | 1 380 000 | 15% | 8% |
| Наурыз | 1 242 000 | −10% | 12% |
Өсім формуласы: =IFERROR(B3/B2-1;""). Өткен жылмен салыстыру үшін өткен жылдың сол айының сомасы бөлек қажет: =SUMIFS($E:$E;$G:$G;A2;$H:$H;жыл-1), содан кейін =B2/C2-1.
Үздік санаттар және контрагенттер
Рейтинг үшін алдымен әр санаттың жылдық сомасын табасыз, содан кейін ең үлкендерін шығарасыз:
- N-ші үлкен мән:
=LARGE(сома_аймағы;1),=LARGE(сома_аймағы;2)және т.б.; - сол мәнге сәйкес атауы:
=INDEX(атау_аймағы;MATCH(мән;сома_аймағы;0)).
Екі санаттың сомасы бірдей болса, MATCH ең алғашқысын қайтарады. Мұны болдырмау үшін сомаға өте кішкентай қосындыны қосып, ажыратуға болады (мысалы, жолдың нөмірін миллионға бөліп). «Санат × ай» матрицасы да солай жасалады: жолдарда санаттар, бағандарда айлар, қиылыста SUMIFS. Оған шартты пішімдеу арқылы түс шкаласын қойсаңыз, қай айда қай санаттың көбірек екені бірден көрінеді.
Деректердің дұрыстығын тексеру
Жиынтық жүктеме қаншалықты таза болса, соншалықты дұрыс. Төрт тексеру жасаңыз:
- Күні бос жолдар:
=COUNTBLANK(A2:A5000). - Сан орнында мәтін:
=SUMPRODUCT(--ISTEXT(E2:E5000)). - Кезеңнен тыс күндер:
=COUNTIFS(A2:A5000;"<"&DATE(жыл;1;1))+COUNTIFS(A2:A5000;">"&DATE(жыл;12;31)). - Қайталанулар: көмекші бағанда
=COUNTIFS(A:A;A2;B:B;B2;C:C;C2;E:E;E2)>1.
Егер қорытынды жиынтық сомасы жүктемедегі жалпы сомаға (=SUM(E:E)) тең болмаса, бір жерде қате бар. Бұл қарапайым бақылау көп сағат үнемдейді.
Жиі кездесетін қателер
- Жүктеме парағына формулаларды тікелей жазу: келесі жүктемені қойған кезде олар өшеді.
- Санаттарды әртүрлі жазу («Тауар А», «тауар а», «Тауар А »): SUMIFS бұларды бөлек санайды.
- Күндерді мәтін ретінде қалдыру.
- Жыл мен кезеңді қатырып жазу, жаңа жылда формулаларды қайта өңдеу.
- Жиынтықты тексерусіз басшыға жіберу.
Дайын үлгі: жүктемеден әмбебап есеп
Бәрін өзіңіз құрғыңыз келмесе, дайын кестені алуға болады. Үлгі ақылы және бес парақтан тұрады.
«Параметрлер» парағында есеп жылы және жүктемеңіздің баған атаулары беріледі: сіздің жүктеме қандай бағандармен келсе, соларға бапталады. «Деректер» парағында тақырып және қоюға арналған 5000 жол бар, ай, жыл және кілттер сияқты қызметтік бағандар алдын ала есептелген. «Жиынтық» парағында айлар бойынша қорытындылар, алдыңғы айға және өткен жылдың сол айына өсім, үздік 5 санат, үздік 10 контрагент, «санат × ай» матрицасы, бағанды және дөңгелек диаграммалар бар. «Кезеңді таңдау» парағы таңдалған санаттар бойынша кез келген кезеңге сома мен санды береді. «Деректерді тексеру» парағы бос күндерді, санның орнындағы мәтінді, кезеңнен тыс күндерді және қайталануларды есептегіштермен көрсетеді.
Күні, санаты, саны және сомасы бар кез келген кестені қоюға болады, мысалы 1С жүктемесін. Шектеулер: 5000 жолға, 30 санатқа және 200 контрагентке дейін. Файл қазақ тілінде, Excel 2007 және одан жаңа нұсқаларда және LibreOffice-те макростарсыз және жаңа функцияларсыз жұмыс істейді. Бухгалтерге, менеджерге, бөлімше басшысына және жүктемелерден жиынтықты қолмен жасайтындардың бәріне қолайлы.
Жиі қойылатын сұрақтар
Жиынтықты жаңа жүктемемен қалай жаңартамын?
Деректер парағындағы ескі жолдарды тазалап, жаңа жүктемені тақырыбының астына қойыңыз. Жиынтық өзі есептеледі, ал тексеру парағы мәселелі жолдарды көрсетеді.
Жүктемеде баған атаулары басқаша болса не істеу керек?
Парақтағы баған атауларын параметрлерде өзіңіздің атауларыңызға ауыстырыңыз. Қолмен бағандарды қайта орналастырудың қажеті жоқ.
Pivot-кестенің орнына формулалар не үшін керек?
Pivot-кесте де жарайды, бірақ әр жүктемеден кейін оны жаңартып, баптауды қайталауға тура келеді. Формулалы жиынтық жүктемені ауыстырумен-ақ жаңарады.
Қанша жолды өңдеуге болады?
Дайын үлгіде 5000 жолға дейін. Одан көп болса, жүктемені жарты жылға бөліп, екі файлға қойыңыз.
