змест
Зводныя табліцы добрыя для ўсіх - яны хутка разлічваюць і гнутка наладжваюцца, і пры неабходнасці ў іх можна элегантна ўключыць дызайн. Але ёсць і некаторыя кропкі дэгцю, у прыватнасці, немагчымасць стварыць зводку, дзе вобласць значэнняў павінна ўтрымліваць не лічбы, а тэкст.
Паспрабуем абысці гэтае абмежаванне і прыдумаць «пару мыліц» у падобнай сітуацыі.
Дапусцім, наша кампанія вязе сваю прадукцыю ў кантэйнерах у некалькі гарадоў нашай краіны і Казахстана. Кантэйнеры адпраўляюцца не часцей за адзін раз у месяц. Кожны кантэйнер мае літарна-лічбавы нумар. У якасці зыходных дадзеных маецца стандартная табліца паставак, з якой трэба зрабіць нейкую зводку, каб наглядна бачыць колькасць кантэйнераў, адпраўленых у кожны горад і кожны месяц:
Для зручнасці загадзя зробім табліцу з зыходнымі дадзенымі «разумнай» камандай Галоўная - фармат у выглядзе табліцы (Галоўная — фармат у табліцу) і даць ёй імя пастаўкі таб Канструктар (Дызайн). У будучыні гэта спросціць жыццё, т.к. можна будзе выкарыстоўваць назву табліцы і яе слупкоў непасрэдна ў формулах.
Спосаб 1. Самы просты - выкарыстоўваць Power Query
Power Query - гэта супермагутны інструмент для загрузкі і пераўтварэння даных у Excel. Гэтая надбудова была ўбудавана ў Excel па змаўчанні з 2016 г. Калі ў вас ёсць Excel 2010 або 2013, вы можаце спампаваць і ўсталяваць яе асобна (цалкам бясплатна).
Увесь працэс, для нагляднасці, я паэтапна разабраў у наступным відэа:
Калі няма магчымасці выкарыстоўваць Power Query, то можна пайсці іншымі шляхамі - з дапамогай зводнай табліцы або формул.
Спосаб 2. Дапаможны канспект
Давайце дадамо яшчэ адзін слупок у нашу зыходную табліцу, дзе з дапамогай простай формулы вылічым колькасць кожнага радка ў табліцы:
Відавочна, што -1 неабходны, таму што ў нашай табліцы ёсць аднарадковы загаловак. Калі ваша табліца знаходзіцца не ў пачатку ліста, то вы можаце скарыстацца крыху больш складанай, але універсальнай формулай, якая вылічвае розніцу ў ліках бягучага радка і загалоўка табліцы:
Зараз стандартным спосабам мы пабудуем зводную табліцу патрэбнага вам тыпу на аснове нашых дадзеных, але ў полі значэння мы апусцім поле Нумар радка замест таго, што мы хочам кантэйнер:
Паколькі ў нас няма некалькіх кантэйнераў у адным горадзе ў адным месяцы, у нашай зводцы, па сутнасці, будзе выдавацца не колькасць, а нумары радкоў патрэбных кантэйнераў.
Акрамя таго, вы можаце адключыць агульныя і прамежкавыя вынікі на ўкладцы Канструктар – Агульныя сумы и Прамежкавыя вынікі (Дызайн — агульныя вынікі, прамежкавыя вынікі) і там жа кнопкай пераключыць зводку на больш зручную табліцу Макет справаздачы (Макет справаздачы).
Такім чынам, мы ўжо на паўдарогі да выніку: у нас ёсць табліца, дзе на скрыжаванні горада і месяца ў зыходнай табліцы знаходзіцца нумар радка, дзе ляжыць патрэбны нам код кантэйнера.
Зараз давайце скапіруем зводку (на той жа аркуш або іншы) і ўставім яе ў якасці значэнняў, а потым увядзем нашу формулу ў вобласць значэнняў, якая выцягне код кантэйнера па нумары радка, знойдзенага ў зводцы:
функцыя IF (КАЛІ), у гэтым выпадку правярае, што наступная ячэйка ў зводцы не пустая. Калі пустая, то вывесці пусты тэкставы радок «», г.зн. пакінуць ячэйку пустой. Калі не пусты, то выняць са слупка Кантэйнер зыходная табліца пастаўкі змест ячэйкі па нумары радка з дапамогай функцыі індэкс (ІНДЭКС).
Бадай, адзіны не вельмі відавочны момант тут - падвойнае слова Кантэйнер у формуле. Такая дзіўная форма напісання:
расходныя матэрыялы[[Кантэйнер]:[Кантэйнер]]
... патрэбны толькі для спасылкі на слупок Кантэйнер была абсалютнай (як спасылка са знакамі $ для звычайных «неразумных» табліц) і не спаўзала ў суседнія слупкі пры капіяванні нашай формулы справа.
У далейшым пры змене дадзеных у зыходнай табліцы пастаўкі, мы не павінны абнавіць нашу дапаможную зводку нумарамі радкоў, пстрыкнуўшы яе правай кнопкай мышы і выбраўшы каманду Абнавіць і захаваць (Абнавіць).
Спосаб 3. Формулы
Гэты метад не патрабуе стварэння прамежкавай зводнай табліцы і ручнога абнаўлення, але выкарыстоўвае «цяжкую зброю» Excel - функцыю САММЕСЛІМН (СУМЫ). Замест таго, каб шукаць нумары радкоў у зводцы, вы можаце вылічыць іх па гэтай формуле:
Фактычна, з некаторай знешняй грувасткасцю, гэта стандартны варыянт выкарыстання функцыі выбарачнага сумавання САММЕСЛІМНA, які сумуе нумары радкоў для дадзенага горада і месяца. Зноў жа, паколькі ў нас няма некалькіх кантэйнераў у адным горадзе ў адным месяцы, наша функцыя будзе, па сутнасці, выдаваць не суму, а сам нумар радка. А затым функцыя, ужо знаёмая з папярэдняга спосабу індэкс Вы таксама можаце атрымаць коды кантэйнераў:
Вядома, у гэтым выпадку вам больш не трэба думаць аб абнаўленні зводкі, але на вялікіх табліцах функцыя СУМЭСЛІ можа быць прыкметна павольным. Тады вам прыйдзецца адключыць аўтаматычнае абнаўленне формул, або выкарыстоўваць першы спосаб - зводную табліцу.
Калі знешні выгляд зводкі не вельмі падыходзіць для вашай справаздачы, то вы можаце выцягнуць з яе нумары радкоў у выніковую табліцу не наўпрост, як мы зрабілі, а з дапамогай функцыі GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Як гэта зрабіць можна даведацца тут.
- Як стварыць справаздачу з дапамогай зводнай табліцы
- Як наладзіць разлікі ў зводных табліцах
- Выбарачны падлік з дапамогай SUMIFS, COUNTIFS і г.д.