1. Пошук по мітках

Уявімо собі таблицю, в якій в рядках упереміш вказані назви відділів (або рахунків, або ще чогось).

Підсумовуємо осередки за критерієм
Необхідно обчислити загальну суму по кожному відділу. Багато хто робить це за допомогою фільтра і записи ручками в осередку.
Хоча зробити це можна легко і просто за допомогою всього однієї функції - СУММЕСЛИ.
СУММЕСЛИ (SUMIF) - Підсумовує осередки, що задовольняють заданій умові (умова можна задати тільки одне). Цю функцію так само можна застосувати, якщо таблиця розбита в шпальтах на періоди (помісячно, в кожному місяці по три стовпці - Дохід | Витрата | Різниця) і необхідно підрахувати загальну суму за всі періоди тільки за доходами, видатками і різниця.

Всього для СУММЕСЛИ передбачено три аргументи: Діапазон, Критерій, Діапазон_Суммірованія.
= СУММЕСЛИ (A1: A20000; A1; B1: B20000)
= SUMIF (A1: A20000, A1, B1: B20000)

• Діапазон (A1: A20000) - вказується діапазон з критеріями. Тобто стовпець, в якому шукати значення, вказане аргументом Критерій.
• Критерій (A1) - значення (текстове або числове, а так само дата), яке необхідно знайти в Діапазоні. Може містити символи підстановки "*" і "?". Тобто вказавши в якості Критерію "* маса *" будуть підсумовані значення, в яких зустрічається слово "маса". При цьому слово "маса" може або зустрічатися в будь-якому місці тексту, або в комірці може бути тільки одне це слово. А вказавши "маса *", будуть підсумовані всі значення, що починаються на "маса". "?" - замінює лише один символ, тобто вказавши "мас? а" ви зможете підсумувати рядки і багатозначно "маса" і багатозначно "маска" і т.д.
  Якщо критерій записаний в осередку і треба все ж параметри групових символів, то можна зробити посилання на цей осередок додавши потрібну. Припустимо, треба підсумувати значення, що містять слово "підсумок". Слово "підсумок" записано в комірці A1, в стовпці A при цьому можуть зустрічатися різні за написанням значення, що містять слово "підсумок": "підсумки за червень", "підсумки за липень", "підсумки за березень". Формула тоді повинна виглядати так:
  = СУММЕСЛИ (A1: A20000; "*" & A1 & "*"; B1: B20000)
  "*" & A1 & "*" - знак & (амперсанд) об'єднує кілька значень в одне. Тобто в результаті вийде "* підсумок *".
  Щоб краще зрозуміти принцип роботи формул краще використовувати інструмент Обчислити формулу: Як переглянути етапи обчислення формул
  Всі текстові критерії і критерії з логічними і математичними знаками необхідно укладати в подвійні лапки (= СУММЕСЛИ (A1: A20000; "підсумок"; B1: B20000)). Якщо критерієм є число, використовувати лапки не потрібно. Якщо потрібно знайти безпосередньо знак питання або зірочку, необхідно поставити перед ним знак "тильди" (~).
  Про тильду і її особливості можна дізнатися в цій статті: Як замінити / видалити / знайти зірочку?
• Діапазон_Суммірованія (B1: B20000) (необов'язковий аргумент) - вказується діапазон сум або числових значень, які необхідно підсумувати.

Як це працює: функція шукає в Діапазоні значення, вказане аргументом Критерій, і при знаходженні збіги підсумовує дані, зазначені аргументом Діапазон_Суммірованія. Тобто якщо у нас в стовпці А назва відділу, а в стовпці В суми, то вказавши в якості критерію "Відділ розвитку" результатом функції буде сума всіх значень стовпця В, навпроти яких у стовпці А зустрічається "Відділ розвитку". Фактично Діапазон_Суммірованія може не збігатися за розміром з аргументом Діапазон і помилки самої функції це не викличе. Однак при визначенні осередків для підсумовування, в якості початкової комірки для підсумовування буде використана верхня ліва комірка аргументу Діапазон_Суммірованія, а потім сумуються осередки, відповідні за розміром і формою аргументу Діапазон.

деякі особливості
Останній аргумент функції (Діапазон_Суммірованія - B1: B20000) є необов'язковим. А це означає, що його можна не вказувати. Якщо його не вказати, то функція підсумує значення, зазначені аргументом Діапазон. Для чого це потрібно. Наприклад, Вам необхідно отримати суму тільки тих чисел, які більше нуля. У стовпці А суми. Тоді функція буде мати такий вигляд:
= СУММЕСЛИ (A1: A20000; "> 0")

Що слід варто враховувати: діапазон_суммірованія і діапазон повинні бути рівні за кількістю рядків. Інакше можна отримати невірний результат. Оптимально, якщо це буде виглядати як в наведених мною формулах: діапазон і діапазон_суммірованія починаються з одного рядка і мають однакову кількість рядків: A1: A20000; B1: B20000

Підсумовування по двом і більш критеріям
Але що робити, коли критеріїв для підсумовування 2 і більше? Припустимо, Вам треба підсумувати тільки ті суми, які відносяться до одного відділу і тільки за певну дату. Щасливі володарі версій офісу 2007 і вище можуть скористатися функцією СУММЕСЛІМН:
= СУММЕСЛІМН ($ C $ 2: $ C $ 50; $ A $ 2: $ A $ 50; $ I $ 3; $ B $ 2: $ B $ 50; $ H8)
$ C $ 2: $ C $ 50 - діапазон_суммірованія. Першим аргументів вказується діапазон комірок, що містять суми, які і будуть збиратися в одну.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Діапазон_крітерія. Вказується діапазон комірок, в яких необхідно шукати збіг за критерієм.
$ I $ 3, $ H8 - критерій. Тут, як і в СУММЕСЛИ, допускається вказівка символів підстановки * і? і працюють вони так само.

Особливість вказівки аргументів: спочатку вказується діапазон критерію (вони пронумеровані) потім через точку-з-коми вказується безпосередньо значення (критерій), яке в цьому діапазоні необхідно знайти - $ A $ 2: $ A $ 50; $ I $ 3. І ніяк інакше. Не варто намагатися спочатку вказати всі діапазони, а потім критерії до них - функція видасть або помилку, або підсумує не те, що треба.

Всі умови порівнюються за принципом І. Це означає, що якщо всі перераховані умови виконуються. Якщо хоч одна умова не виконується - функція пропускає рядок і нічого не підсумовує.
Так само як і для СУММЕСЛИ діапазони підсумовування і критеріїв повинні бути рівні за кількістю рядків.

Оскільки СУММЕСЛІМН з'явилася тільки в версіях Excel, починаючи з 2007, то як же бути в таких випадках нещасним користувачам більш ранніх версій? Дуже просто: використовувати іншу функцію - СУММПРОИЗВ. Не буду розписувати аргументи, тому що їх багато і всі вони є масивами значень. Ця функція перемножує масиви, зазначені аргументами. Я постараюся описати загальний принцип використання цієї функції для підсумовування даних по декількох умовах.
Для вирішення завдання підсумовування за кількома критеріями функція буде виглядати так:
= СУММПРОИЗВ (($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5); $ C $ 2: $ C $ 50)
$ A $ 2: $ A $ 50 - діапазон дат. $ I $ 3 - дата критерію, за яку необхідно підсумувати дані.
$ B $ 2: $ B $ 50 - найменування відділів. H5 - найменування відділу, дані по якому необхідно підсумувати.
$ C $ 2: $ C $ 50 - діапазон з сумами.

Розберемо логіку, тому що багатьом вона буде зовсім не ясна просто при погляді на цю функцію. Хоча б тому, що в довідці подібне її застосування не описується. Для більшої читабельності зменшимо розміри діапазонів:
= СУММПРОИЗВ (($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5); $ C $ 2: $ C $ 5)
Отже, вираз ($ A $ 2: $ A $ 5 = $ I $ 3) і ($ B $ 2: $ B $ 5 = H5) є логічними і повертають масиви логічних БРЕХНЯ і ІСТИНА. ІСТИНА, якщо осередок діапазону $ A $ 2: $ A $ 5 дорівнює значенню комірки $ I $ 3 і осередок діапазону $ B $ 2: $ B $ 5 дорівнює значенню комірки H5. Тобто виходить у нас наступне:
= СУММПРОИЗВ ({БРЕХНЯ; ІСТИНА; ІСТИНА; БРЕХНЯ} * {БРЕХНЯ; БРЕХНЯ; ІСТИНА; БРЕХНЯ}; $ C $ 2: $ C $ 50)
Як видно, в першому масиві два збіги умові, а в другому одне. Далі ці два масиви перемножуються (за це відповідає знак множення (*)). При перемноження відбувається неявне перетворення масивів БРЕХНЯ і ІСТИНА в числові константи 0 і 1 відповідно ({0; 1; 1; 0} * {0, 0, 1, 0}). Як відомо, при множенні на нуль отримуємо нуль. І в результаті виходить один масив:
= СУММПРОИЗВ ({0, 0, 1, 0}; $ C $ 2: $ C $ 50)
Далі відбувається вже множення масиву {0, 0, 1, 0} на масив чисел в діапазоні $ C $ 2: $ C $ 50:
= СУММПРОИЗВ ({0, 0, 1, 0}; {10; 20; 30; 40})
І як результат отримуємо 30. Що нам і потрібно - ми отримуємо лише ту суму, яка відповідає критерію. Якщо сум, що задовольняють критерію буде більше однієї, то вони будуть підсумовані.

перевага СУММІРОІЗВ
Якщо у аргументів замість знака множення вказати знак плюс:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
то умови будуть порівнюватися за принципом АБО: тобто підсумовуватися підсумкові суми будуть в разі, якщо хоча б одна умова виконується: або $ A $ 2: $ A $ 5 дорівнює значенню комірки $ I $ 3 або осередок діапазону $ B $ 2: $ B $ 5 дорівнює значенню комірки H5.
У цьому перевага СУММПРОИЗВ перед СУММЕСЛІМН. СУММЕСЛІМН не може підсумовувати значення за принципом АБО, тільки за принципом І (всі умови повинні виконуватися).

недоліки
У СУММПРОИЗВ неможливо використовувати символи підстановки * і?. Точніше використовувати можна, але вони будуть сприйняті не як спец.символов, а як безпосередньо зірочка і вопр.знак. Я вважаю це суттєвим мінусом. І хоча це можна обійти, використовую всередині СУММПРОИЗВ інші функції - все ж було б чудово, якби функція якимось чином могла використовувати символи підстановки.

У прикладі знайдете пару прикладів функцій для більш кращого розуміння написаного вище.

завантажити приклад

Сума за кількома критеріями (41,5 KiB, 10 477 завантажень)

Так само див .:
Підсумовування осередків за кольором заливки
Підсумовування осередків за кольором шрифту
Підсумовування осередків по формату комірки
Підрахувати суму осередків за кольором заливки
Підрахувати суму осередків за кольором шрифту
Як підсумувати дані з декількох листів, в тому числі за умовою

{ "Bottom bar": { "textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1" 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background-color: # 333333; opacity: 0.6; filter: a lpha (opacity = 60); "," titlecss ":" display: block; position: relative; font: bold 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; position: relative; font: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; margin-top: 8px; "," buttoncss ":" display: block; position: relative; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none! important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Пошук по мітках