Ҷадвалҳои пивот барои ҳама хубанд - онҳо зуд ҳисоб мекунанд ва ба таври чандир танзим карда мешаванд ва дар ҳолати зарурӣ тарроҳиро ба таври шево печонида метавонанд. Аммо дар атрафшон низ чанд магасе вуҷуд дорад, аз ҷумла, натавонистани як хулосаи эҷод, ки дар майдони арзиши бояд рақамҳо, балки матнро дар бар намегирад.
Биёед кӯшиш кунем, ки ин маҳдудиятро паси сар кунем ва дар вазъияти ба ин монанд "як ҷуфт асобаро" пайдо кунем.
Фарз мекунем, ки ширкати мо маҳсулоти худро дар контейнерҳо ба якчанд шаҳрҳои кишвари мо ва Қазоқистон мекашонад. Контейнерҳо на бештар аз як маротиба дар як моҳ фиристода мешаванд. Ҳар як контейнер дорои рақами алифбоӣ мебошад. Ҳамчун маълумоти ибтидоӣ, ҷадвали стандартии номгӯи интиқол мавҷуд аст, ки аз он шумо бояд як навъ хулоса тартиб диҳед, то рақами контейнерҳои ба ҳар як шаҳр ва ҳар моҳ фиристодашударо равшан бубинед:
Барои роҳат, биёед бо истифода аз фармон ҷадвалро бо маълумоти ибтидоӣ пешакӣ «ақл» кунем Хона - Формат ҳамчун ҷадвал (Хона — Формат ҳамчун ҷадвал) ва ба вай ном диҳед Расонидани ҷадвали Конструктор (Тарроҳӣ). Дар оянда ин хаётро осон мекунад, зеро. номи чадвал ва сутунхои онро бевосита дар формулахо истифода бурдан мумкин мешавад.
Усули 1. Осонтарин – истифодаи Power Query
Power Query як воситаи пурқувват барои бор кардан ва табдил додани маълумот дар Excel мебошад. Ин илова ба таври нобаёнӣ дар Excel аз соли 2016 сохта шудааст. Агар шумо Excel 2010 ё 2013 дошта бошед, шумо метавонед онро алоҳида зеркашӣ ва насб кунед (комилан ройгон).
Тамоми раванд, барои равшанӣ, ман қадам ба қадам дар видеои зерин таҳлил кардам:
Агар истифодаи Power Query имконнопазир бошад, шумо метавонед бо роҳҳои дигар - тавассути ҷадвали ҷамъоварӣ ё формулаҳо гузаред.
Усули 2. Хулосаи ёрирасон
Биёед ба ҷадвали аслии худ як сутуни дигар илова кунем, ки дар он бо истифода аз формулаи оддӣ шумораи ҳар як сатри ҷадвал ҳисоб карда мешавад:
Аён аст, ки -1 лозим аст, зеро мо дар ҷадвали мо сарлавҳаи як сатр дорем. Агар ҷадвали шумо дар аввали варақ набошад, шумо метавонед формулаи каме мураккабтар, вале универсалиро истифода баред, ки фарқияти рақамҳои сатри ҷорӣ ва сарлавҳаи ҷадвалро ҳисоб мекунад:
Ҳоло, ба таври стандартӣ, мо дар асоси маълумоти худ як ҷадвали пивоти навъи дилхоҳро месозем, аммо дар майдони арзиш мо майдонро мепартоем. Рақами сатр ба ҷои он чизе ки мо мехоҳем контейнер:
Азбаски дар як моҳ дар як шаҳр чанд контейнер надорем, хулосаи мо воқеан на маблағ, балки рақами сатри контейнерҳои ба мо лозимиро медиҳад.
Илова бар ин, шумо метавонед миқдор ва зермаҷмӯҳоро дар ҷадвал хомӯш кунед Сохтмон - Ҷамъбасти умумӣ и Мобайнҳо (Тарҳрезӣ — Ҷамъи умумӣ, Ҷамъбастҳо) ва дар ҳамон ҷо хулосаро ба тарҳбандии ҷадвали мувофиқтар бо тугма иваз кунед Ҳисоботи намунавӣ (Тарҳбанди гузориш).
Ҳамин тариқ, мо аллакай дар нисфи роҳ ба натиҷа расидаем: мо ҷадвал дорем, ки дар чорроҳаи шаҳр ва моҳ рақами сатр дар ҷадвали манбаъ мавҷуд аст, ки дар он рамзи контейнер ба мо лозим аст.
Акнун биёед хулосаро нусхабардорӣ кунем (ба ҳамон варақ ё дигар) ва онро ҳамчун арзишҳо часбонем ва сипас формулаи моро ба майдони арзиш ворид кунед, ки он рамзи контейнерро аз рӯи рақами сатри дар хулоса мавҷудбуда берун мекунад:
функсия IF (АГАР), дар ин ҳолат, тафтиш мекунад, ки чашмаки навбатии хулоса холӣ нест. Агар холӣ бошад, пас сатри матнии холии ""-ро бароред, яъне чашмакро холӣ гузоред. Агар холӣ набошад, пас аз сутун хориҷ кунед Контейнер ҷадвали манбаъ Расонидани мундариҷаи ҳуҷайра аз рӯи рақами сатр бо истифода аз функсия INDEX (ИНДЕКС).
Шояд ягона нуктаи на он қадар равшан дар ин ҷо калимаи дугона бошад Контейнер дар формула. Чунин шакли аҷиби навиштан:
Таҷҳизот[[Контейнер]:[Контейнер]]
… танҳо барои истинод ба сутун лозим аст Контейнер мутлақ буд (ба монанди истинод бо аломатҳои $ барои ҷадвалҳои оддии "ғайриақл") ва ҳангоми нусхабардории формулаи мо ба тарафи рост ба сутунҳои ҳамсоя намеафтид.
Дар оянда, ҳангоми тағир додани маълумот дар ҷадвали манбаъ Расонидани, мо бояд дар хотир дорем, ки хулосаи ёрирасони худро бо рақамҳои сатр бо пахш кардани тугмаи рости он ва интихоби фармон навсозӣ кунем. Навсозӣ ва Захира (навсозӣ).
Усули 3. Формулаҳо
Ин усул эҷоди ҷадвали фосилавӣ ва навсозии дастиро талаб намекунад, аммо "силоҳи вазнин" -и Excel -ро истифода мебарад. SUMMESLIMN (SUMIFS). Ба ҷои ҷустуҷӯи рақамҳои сатр дар хулоса, шумо метавонед онҳоро бо ин формула ҳисоб кунед:
Бо як миқдори зиёди беруна, дар асл, ин як ҳолати истифодаи стандартии функсияи ҷамъбастии интихобӣ мебошад SUMMESLIMNA, ки рақамҳои сатрро барои шаҳр ва моҳи додашуда ҷамъбаст мекунад. Боз ҳам, азбаски мо дар як моҳ дар як шаҳр чанд контейнер надорем, функсияи мо воқеан на маблағ, балки худи рақами сатрро медиҳад. Ва он гоҳ функсияе, ки аллакай аз усули қаблӣ шинос аст INDEX Шумо инчунин метавонед рамзҳои контейнерро истихроҷ кунед:
Албатта, дар ин ҳолат, ба шумо дигар лозим нест, ки дар бораи навсозии хулоса фикр кунед, аммо дар ҷадвалҳои калон, функсия СУММЕСЛИ ба таври назаррас суст шуда метавонад. Он гоҳ ба шумо лозим меояд, ки навсозии автоматии формулаҳоро хомӯш кунед ё усули аввалро истифода баред - ҷадвали ҷамъоварӣ.
Агар намуди мухтасар барои гузориши шумо чандон мувофиқ набошад, шумо метавонед рақамҳои сатрро аз он ба ҷадвали ниҳоӣ на мустақиман, чуноне ки мо кардем, балки бо истифода аз функсия бароред. GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Чӣ тавр ин корро кардан мумкин аст дар ин ҷо пайдо шавад.
- Чӣ тавр эҷод кардани гузориш бо истифода аз ҷадвали pivot
- Чӣ тавр ҳисобҳоро дар ҷадвалҳои пивот танзим кардан мумкин аст
- Ҳисобкунии интихобӣ бо SUMIFS, COUNTIFS ва ғайра.