Жиынтық кестелер барлығына қолайлы – олар тез есептеліп, икемді түрде конфигурацияланады және қажет болған жағдайда дизайн оларға талғампаздықпен оралуы мүмкін. Бірақ сонымен қатар жақпада бірнеше шыбын бар, атап айтқанда, қорытынды жасау мүмкін емес, мұнда құндылық аймағында сандар емес, мәтін болуы керек.
Осы шектеуді айналып өтіп, ұқсас жағдайда «бір-екі балдақ» ойлап көрейік.
Біздің компания өз өнімдерін контейнерлерде Еліміздің және Қазақстанның бірнеше қаласына тасымалдайды делік. Контейнерлер айына бір реттен жиі емес жіберіледі. Әрбір контейнерде әріптік-сандық нөмір болады. Бастапқы деректер ретінде әр қалаға және әр айға жіберілген контейнерлердің санын нақты көру үшін қандай да бір қорытынды жасау қажет жеткізілімдерді көрсететін стандартты кесте бар:
Ыңғайлы болу үшін пәрменді пайдаланып, бастапқы деректері бар кестені алдын ала «ақылды» етіп алайық Басты – кесте ретінде пішімдеу (Үй — Кесте ретінде пішімдеу) және оған ат беріңіз Жеткізу қойындысы Конструктор (Дизайн). Болашақта бұл өмірді жеңілдетеді, өйткені. кестенің атын және оның бағандарын тікелей формулаларда қолдануға болады.
1-әдіс. Ең оңай жолы – Power Query пайдаланыңыз
Power Query — Excel бағдарламасындағы деректерді жүктеуге және түрлендіруге арналған өте қуатты құрал. Бұл қондырма әдепкі бойынша Excel бағдарламасына 2016 жылдан бастап енгізілген. Егер сізде Excel 2010 немесе 2013 болса, оны бөлек жүктеп алуға және орнатуға болады (толығымен тегін).
Бүкіл процесс, түсінікті болу үшін мен келесі бейнеде кезең-кезеңмен талдадым:
Егер Power Query пайдалану мүмкін болмаса, онда басқа жолдармен – жиынтық кесте немесе формулалар арқылы өтуге болады.
2-әдіс. Көмекші түйіндеме
Түпнұсқа кестеге тағы бір баған қосамыз, мұнда қарапайым формуланы пайдаланып кестедегі әрбір жолдың санын есептейміз:
Әлбетте, -1 қажет, өйткені біздің кестеде бір жолды тақырып бар. Егер кестеңіз парақтың басында болмаса, ағымдағы жол мен кесте тақырыбының сандарындағы айырмашылықты есептейтін сәл күрделірек, бірақ әмбебап формуланы қолдануға болады:
Енді стандартты түрде біз деректер негізінде қалаған түрдегі жиынтық кестені құрастырамыз, бірақ мән өрісінде өрісті тастаймыз. Жол нөмірі біз қалағанның орнына контейнер:
Бізде бір айда бір қалада бірнеше контейнер болмағандықтан, біздің жиынтықта, шын мәнінде, бізге қажетті контейнерлердің сомасын емес, жол нөмірлерін береді.
Бұған қоса, қойындыдағы жалпы және аралық қорытындыларды өшіруге болады Конструктор – Жалпы қорытындылар и Аралық қорытындылар (Дизайн — Жалпы қорытындылар, аралық қорытындылар) және сол жерде түйме арқылы қорытындыны ыңғайлырақ кесте орналасуына ауыстырыңыз Есеп үлгісі (Есептің орналасуы).
Осылайша, біз нәтижеге жарты жолда қалдық: бізде қала мен айдың қиылысында бастапқы кестеде жол нөмірі бар, бізге қажет контейнер коды орналасқан кесте бар.
Енді жиынтықты көшіріп алайық (сол немесе басқа параққа) және оны мәндер ретінде қоямыз, содан кейін жиынтықта табылған жол нөмірі бойынша контейнер кодын шығаратын мән аймағына формуламызды енгізейік:
функция IF (Егер), бұл жағдайда қорытындыдағы келесі ұяшық бос емес екенін тексереді. Егер бос болса, «» бос мәтін жолын шығарыңыз, яғни ұяшықты бос қалдырыңыз. Егер бос болмаса, бағаннан шығарып алыңыз контейнер бастапқы кесте Жеткізу функцияны пайдаланып жол нөмірі бойынша ұяшық мазмұны INDEX (ИНДЕКС).
Мүмкін, бұл жерде бірден-бір анық емес нәрсе - қос сөз контейнер формулада. Жазудың біртүрлі түрі:
Жабдықтар[[Контейнер]:[Контейнер]]
… тек бағанға сілтеме жасау үшін қажет контейнер абсолютті болды (қарапайым «ақылды емес» кестелер үшін $ белгілері бар анықтама сияқты) және формуламызды оңға көшіру кезінде көрші бағандарға сырғымады.
Болашақта бастапқы кестедегі деректерді өзгерту кезінде Жеткізу, тінтуірдің оң жақ түймешігімен басып, пәрменді таңдау арқылы көмекші түйіндемені жол нөмірлерімен жаңартуды ұмытпауымыз керек. Жаңарту және сақтау (Жаңарту).
3-әдіс. Формулалар
Бұл әдіс аралық жиынтық кестені құруды және қолмен жаңартуды қажет етпейді, бірақ Excel бағдарламасының «ауыр қаруы» функциясын пайдаланады. SUMMESLIMN (SUMIFS). Жиынтықта жол нөмірлерін іздеудің орнына оларды мына формула арқылы есептеуге болады:
Кейбір сыртқы көлемділікпен, шын мәнінде, бұл таңдамалы жиынтық функциясы үшін стандартты пайдалану жағдайы SUMMESLIMNБерілген қала мен ай үшін жол нөмірлерін қосатын A. Қайтадан, бір айда бір қалада бірнеше контейнер болмағандықтан, біздің функция шын мәнінде соманы емес, жол нөмірін береді. Содан кейін функция алдыңғы әдістен бұрыннан таныс INDEX Сондай-ақ контейнер кодтарын шығаруға болады:
Әрине, бұл жағдайда қорытындыны жаңарту туралы ойланудың қажеті жоқ, бірақ үлкен кестелерде функция СУММЕСЛИ айтарлықтай баяу болуы мүмкін. Содан кейін формулаларды автоматты түрде жаңартуды өшіру керек немесе бірінші әдісті - жиынтық кестені пайдалану керек.
Егер түйіндеменің көрінісі сіздің есебіңізге өте қолайлы болмаса, онда сіз одан жол нөмірлерін соңғы кестеге біз жасағандай тікелей емес, функцияны пайдаланып шығара аласыз. GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Мұны қалай жасауға болатынын мына жерден табуға болады.
- Жиынтық кестені пайдаланып есепті құру жолы
- Жиынтық кестелерде есептеулерді қалай орнатуға болады
- SUMIFS, COUNTIFS және т.б. көмегімен таңдаулы санау.