Одне з найпоширеніших питань на дошці повідомлень - як використовувати функцію SumIf за двох різних умов. На жаль, відповідь полягає в тому, що SumIf не може впоратися з двома різними умовами.
Для того, щоб виконати дві умови, вам доведеться використовувати досить складну формулу масиву. Надбудова майстра умовної суми дозволяє легко вводити ці складні формули.
Ось аркуш Excel зі стовпцями для товару, торгового представника та продажів. Дані містяться в клітинках A2: C29.
Якщо ви хочете підсумувати продажі, буде працювати проста функція SUM (). =SUM(C2:C29).
Багато Excellers відкривають функцію SumIf. Використовуючи цю функцію, досить легко визначити загальний обсяг продажів продукту ABC.=SUMIF(A2:A29,E2,C2:C29)
Також легко визначити загальний обсяг продажів, здійснених торговим представником Джо =SUMIF(B2:B29,E2,C2:C29).
Тоді ви могли б припустити, що можна з'ясувати загальний обсяг реалізації продукту ABC, виготовлений Джо. Однак це неможливо зробити за допомогою функції SumIf. Виявляється, потрібно використовувати досить складний масив або формулу CSE.
Погодьмося: формула Сума - це Excel 101. Формула СумІф не відстає у складності. Однак формули CSE для підрахунку загальної кількості продажів ABC, здійснених Джо, достатньо, щоб навіть у мене в голові закрутилося.
Хороша новина - Microsoft пропонує майстер умовної суми, який дозволяє навіть новачкові вводити складні умовні формули на основі 1, 2 або більше умов. Майстер умовної суми - це надбудова. Щоб додати цю функцію до Excel, перейдіть до меню Інструменти та виберіть Надбудови. У діалоговому вікні Надбудови встановіть прапорець поруч із Майстром умовної суми та виберіть OK. Можливо, на цьому етапі вам може знадобитися ваш інсталяційний компакт-диск, оскільки Microsoft не включає майстра в установку за замовчуванням.
Після успішного ввімкнення надбудови внизу меню Інструменти з’явиться вибір Conditonal Sum….
Виберіть одну клітинку у наборі даних і виберіть Інструменти - умовна сума. Припускаючи, що ваші дані добре відформатовані за допомогою одного рядка заголовків, Excel правильно вгадає діапазон ваших даних. Виберіть Далі.
На кроці 2 виберіть стовпець для підсумовування. У цьому випадку майстер вже здогадався, що ви хочете підсумувати перший (і єдиний) числовий стовпець - Продажі. У середині діалогового вікна розташовано три випадаючі елементи керування. Це трапляється правильно для першої умови - виріб дорівнює ABC, тому натисніть кнопку Додати умову.
Тоді ви можете додати свою другу умову. У цьому випадку ви хочете вказати, що торговим представником є Джо. Виберіть стрілку для першого спадного меню. Excel пропонує алфавітний список доступних назв стовпців. Виберіть торгового представника
Випадаючий центр по центру правильний, але для повноти тут ви можете бачити, що ви могли вибрати рівне, менше, більше, менше або рівне, більше або рівне, або не рівне.
З третього спадного меню виберіть Джо.
Виберіть кнопку Додати умову.
Тепер ви готові перейти до кроку 3. Натисніть кнопку Далі.
На кроці 3 у вас є два варіанти. При першому виборі Майстер введе єдину формулу зі значеннями "ABC" та "Joe" із жорстким кодом у формулу. Це дасть вам відповідь, але не буде можливості легко змінити формулу. З другим вибором Excel створить нову комірку зі значенням "ABC" та нову комірку зі значенням "Джо". Третя комірка буде містити формулу, яка складає умовну суму на основі цих двох значень. За допомогою цієї опції ви можете ввести нові значення в клітинки, щоб побачити загальну кількість XYZ, проданих Адамом.
Потім майстер запитає, де потрібно значення для ABC. Виділіть клітинку та натисніть Далі. Повторіть це, коли Майстер просить Вас вибрати комірку для Джо та формулу.
Коли на останньому кроці ви виберете Готово, Excel створить дещо іншу (але дійсну) версію формули CSE.
Ця формула підраховує, що Джо продав 33 338 доларів ABC.
Якщо змінити клітинку для введення товару з ABC на DEF, формула буде перерахована, щоб показати, що Джо продав $ 24 478 DEF.
Майстер умовної суми розміщує складні формули в межах досяжності всіх власників Excel.
Додаткова інформація:Якщо ви хочете скласти таблицю, яка відображатиме продажі кожного товару за кожним торговим представником, існує кілька спеціальних «догляду та годування», які вам потрібно буде знати про ці формули. Введіть кожного торгового представника у верхній частині асортименту. Введіть кожен товар у лівій колонці асортименту. Відредагуйте формулу, надану майстром. На зображенні нижче формула вказує продукт у клітинку Е6. Це посилання справді має становити $ E6. Якщо залишити посилання як E6 і скопіювати формулу в стовпець G, формула буде виглядати на F6 замість E6, і це буде неправильно. Додавши знак долара перед знаком Е у E6, ми впевнимось, що формула завжди розглядає товар у стовпці E. Формула також вказує на торгового представника в комірці F5. Це посилання дійсно має становити 5 доларів. Якщо ви залишили посилання як F5 і скопіюйте до рядка 7,посилання F5 зміниться на F6, і це неправильно. Додавання знака долара перед номером рядка блокує номер рядка, а посилання завжди вказуватиме на рядок 5.
У режимі редагування (виділіть клітинку та натисніть F2 для редагування), введіть $ перед E. Введіть знак долара перед 5 у F5. Не натискайте Enter ще!
Ця формула є особливим типом формул. Якщо натиснути Enter, ви отримаєте 0, що є неправильним.
Замість того, щоб вводити Enter, утримуйте клавіші Ctrl і Shift, одночасно натискаючи Enter. Ця чарівна комбінація C trl + S hift + E nter, тому я називаю ці формули ПЕ .
Перед копіюванням формули до решти таблиці є останній розгляд. Вашим нахилом може бути копіювання F6 та вставлення до F6: G8. Якщо ви спробуєте це, Excel видасть вам загадкове повідомлення "Ви не можете змінити частину масиву". Excel скаржиться, що ви не можете вставити формулу CSE у діапазон, який містить оригінальну формулу CSE.
Це легко обійти. Копія F6. Вставте в F7: F8.
Копія F6: F8. Вставити в G6: G8. Ви отримаєте таблицю формул ПСЕ, що відображає підсумки на основі двох умов.
