Усули 1. Формулаҳо
Биёед, барои гарм кардан, бо варианти соддатарин - формулаҳо оғоз кунем. Агар мо ҷадвали хурде дошта бошем, ки аз рӯи сана ҳамчун воридкунӣ мураттаб карда шуда бошад, пас барои ҳисоб кардани маблағи иҷрошуда дар сутуни алоҳида ба мо формулаи элементарӣ лозим аст:
Хусусияти асосии ин ҷо танзими мураккаби диапазон дар дохили функсияи SUM мебошад - истинод ба оғози диапазон мутлақ (бо аломатҳои доллар) ва то охир нисбӣ (бе доллар) дода мешавад. Мувофиқи он, ҳангоми нусхабардории формула ба тамоми сутун, мо диапазони васеъшавандаро мегирем, ки маблағи онро ҳисоб мекунем.
Камбудиҳои ин усул равшананд:
- Ҷадвал бояд аз рӯи сана ҷудо карда шавад.
- Ҳангоми илова кардани сатрҳои нав бо маълумот, формула бояд дастӣ дароз карда шавад.
Усули 2. Ҷадвали ҷамъбастӣ
Ин усул каме мураккабтар, вале хеле гуворотар аст. Ва барои пурзӯртар кардан, биёед як масъалаи ҷиддитарро баррасӣ кунем - ҷадвали 2000 сатри маълумот, ки дар он ҷо аз рӯи сутуни сана ҷудокунӣ вуҷуд надорад, аммо такрорҳо мавҷуданд (яъне мо метавонем дар як рӯз якчанд маротиба фурӯшем):
Мо ҷадвали аслии худро ба миёнабури клавиатураи "интеллектуалӣ" (динамикӣ) табдил медиҳем Ctrl+T ё даста Хона - Формат ҳамчун ҷадвал (Хона — Формат ҳамчун ҷадвал), ва он гоҳ мо бо фармон дар рӯи он ҷадвали pivot месозем Ворид - Ҷадвали ҷамъбастӣ (Ворид кардан — Ҷадвали ҷамъбастӣ). Мо санаро дар майдони сатрҳо дар хулоса ва шумораи молҳои фурӯхташударо дар минтақаи арзишҳо мегузорем:
Лутфан таваҷҷӯҳ намоед, ки агар шумо версияи на он қадар кӯҳнаи Excel дошта бошед, санаҳо ба таври худкор аз рӯи солҳо, семоҳаҳо ва моҳҳо гурӯҳбандӣ карда мешаванд. Агар ба шумо гурӯҳбандии дигар лозим бошад (ё ба он тамоман лозим набошед), шумо метавонед онро бо клики рости муш дар ягон сана ва интихоби фармонҳо ислоҳ кунед. Гуруҳ / ҷудо кардан (Гурӯҳ / ҷудо кардан).
Агар шумо хоҳед, ки ҳам ҷамъбасти натиҷаҳоро аз рӯи давраҳо ва ҳам ҷамъи ҷорӣро дар сутуни алоҳида бубинед, пас майдонро ба майдони арзиш партоед. Фурӯхта шудааст боз барои гирифтани дубликати майдон - дар он мо намоиши ҷамъбасти иҷрошавандаро фаъол мекунем. Барои ин, дар майдони рост клик кунед ва фармонро интихоб кунед Ҳисобҳои иловагӣ – Маҷмӯи умумии (Нишон додани арзишҳо ҳамчун - Ҷамъи иҷро):
Дар он ҷо шумо инчунин метавонед варианти афзояндаи ҷамъҳоро ҳамчун фоиз интихоб кунед ва дар равзанаи навбатӣ шумо бояд майдонеро интихоб кунед, ки барои он ҷамъкунӣ меравад - дар ҳолати мо ин майдони сана аст:
Афзалиятҳои ин равиш:
- Миқдори зиёди маълумот зуд хонда мешавад.
- Ҳеҷ гуна формулаҳоро дастӣ ворид кардан лозим нест.
- Ҳангоми тағир додани маълумоти манбаъ, бо пахши тугмаи рости муш ё бо фармони Маълумот – Ҳама чизро навсозӣ кардан кофист.
Камбудиҳо аз он бармеоянд, ки ин хулоса аст, ки маънои онро дорад, ки шумо наметавонед ҳар чизеро, ки мехоҳед дар он иҷро кунед (сатрҳо ворид кунед, формулаҳо нависед, ҳама гуна диаграммаҳо созед ва ғайра) дигар кор намекунад.
Усули 3: Дархости барқ
Биёед бо истифода аз фармон ҷадвали "ақл"-и худро бо маълумоти сарчашма ба муҳаррири дархости Power Query бор кунем Маълумот - Аз ҷадвал/диапазон (Маълумот - Аз ҷадвал/диапазон). Дар версияҳои охирини Excel, дар омади гап, он тағир дода шуд - ҳоло он ном дорад Бо баргҳо (Аз варақ):
Пас мо қадамҳои зеринро иҷро мекунем:
1. Ҷадвалро бо тартиби афзоиш аз рӯи сутуни сана бо фармон ҷудо кунед Аз рӯи боло ҷудо кунед дар рӯйхати афтанда филтр дар сарлавҳаи ҷадвал.
2. Каме дертар, барои ҳисоб кардани умумии кор ба мо сутуни ёрирасон бо рақами сатри тартиби лозим аст. Биёед онро бо фармон илова кунем Иловаи Сутун - Сутуни Индекс - Аз 1 (Иловаи сутун - Сутуни индекс - Аз 1).
3. Ғайр аз он, барои ҳисоб кардани ҳаҷми умумии кор ба мо истинод ба сутун лозим аст Фурӯхта шудааст, ки дар он маълумоти ҷамъбастии мо ҷойгир аст. Дар Power Query, сутунҳоро рӯйхатҳо (рӯйхат) низ меноманд ва барои гирифтани истинод ба он сарлавҳаи сутунро бо тугмаи рост клик кунед ва фармонро интихоб кунед. Тафсилот (Намоиши тафсилот). Ифоде, ки ба мо лозим аст, дар сатри формула пайдо мешавад, ки аз номи қадами қаблӣ иборат аст #"Индекс илова карда шуд", аз он ҷо мо ҷадвал ва номи сутунро мегирем [Фурӯш] аз ин ҷадвал дар қавсҳои мураббаъ:
Барои истифодаи минбаъда ин ифодаро ба буфер нусхабардорӣ кунед.
4. Қадами охирини нолозимро нест кунед Фурӯхта шудааст ва ба ҷои он як сутуни ҳисобшударо барои ҳисоб кардани маблағи иҷрошуда бо фармон илова кунед Илова кардани сутун - Сутуни фармоишӣ (Иловаи сутун - Сутуни фармоишӣ). Формулае, ки ба мо лозим аст, чунин хоҳад буд:
Дар ин ҷо функсия Рӯйхат. Диапазон рӯйхати аслиро мегирад (сутун [Фурӯш]) ва аз он элементҳоро аз якум сар карда берун мекунад (дар формула ин 0 аст, зеро рақамгузорӣ дар Power Query аз сифр оғоз мешавад). Миқдори элементҳои дарёфтшаванда рақами сатрест, ки мо аз сутун мегирем [Индекс]. Ҳамин тавр, ин функсия барои сатри аввал танҳо як чашмаки аввали сутунро бармегардонад Фурӯхта шудааст. Барои сатри дуюм - аллакай ду чашмаки аввал, барои сеюм - се аввал ва ғайра.
Хуб, пас функсия Рӯйхат.сум арзишҳои истихроҷшударо ҷамъ мекунад ва мо дар ҳар як сатр маблағи ҳамаи унсурҳои қаблиро мегирем, яъне ҷамъи ҷамъшаванда:
Барои нест кардани сутуни Индекс, ки ба мо дигар лозим нест ва бо фармони Home - Пӯшед ва бор кунед, натиҷаҳоро ба Excel дубора бор кунед.
Мушкилот ҳал шудааст.
Тез ва пурхашмона
Аслан, ин корро боздоштан мумкин буд, аммо дар равған як пашшаи хурде ҳаст - дархосте, ки мо офаридаем, бо суръати сангпушт кор мекунад. Масалан, дар компютери ман заифтарин нест, ҷадвали ҳамагӣ 2000 сатр дар 17 сония коркард мешавад. Чӣ мешавад, агар маълумоти бештар вуҷуд дошта бошад?
Барои суръат бахшидан, шумо метавонед буфериро бо истифода аз функсияи махсуси List.Buffer истифода баред, ки рӯйхатро (рӯйхатро) ҳамчун аргумент ба он додашуда ба RAM бор мекунад, ки дастрасиро ба он дар оянда хеле метезонад. Дар ҳолати мо, буфер кардани рӯйхати #"Индекси иловашуда"[фурӯхташуда] маъно дорад, ки Power Query ҳангоми ҳисоб кардани ҳаҷми умумии кор дар ҳар як сатри ҷадвали 2000-сатри мо ба он дастрасӣ дорад.
Барои ин, дар муҳаррири Power Query дар ҷадвали асосӣ, тугмаи Муҳаррири Advanced (Home - Editor Advanced) -ро клик кунед, то рамзи сарчашмаи дархости моро бо забони M, ки дар Power Query сохта шудааст, кушоед:
Ва он гоҳ як сатри дорои тағирёбанда дар он ҷо илова кунед Рӯйхати ман, ки арзиши он аз ҷониби функсияи буферӣ баргардонида мешавад ва дар қадами оянда мо зангро ба рӯйхат бо ин тағирёбанда иваз мекунем:
Пас аз ворид кардани ин тағирот, дархости мо ба таври назаррас тезтар мешавад ва бо ҷадвали 2000-сатр дар тӯли 0.3 сония мубориза мебарад!
Чизи дигар, дуруст? 🙂
- Диаграммаи Парето (80/20) ва тарзи сохтани он дар Excel
- Ҷустуҷӯи калимаҳои калидӣ дар матн ва буферкунии дархост дар Power Query