Побачивши результат умовного форматування, ви, ймовірно, захочете створити формули, що посилаються тільки на дані, які зазнали умовного форматування. В Excel не дуже добре реалізована така можливість, але ми допоможемо йому.
Користувачі Excel регулярно ставлять запитання, як можна провести обчислення тільки з осередками певного фонового кольору. Це питання виникає так часто, тому що в Excel не передбачена стандартна функція для виконання цього завдання; проте це можна зробити за допомогою користувальницької функції, як демонструє Трюк № 88 «Підрахунок або підсумовування осередків з певним кольором заливки».
Єдина неприємність при використанні для користувача функції - вона не розуміє форматування, накладене за допомогою умовного форматування. Однак, трохи подумавши, ви зможете домогтися потрібного результату, не застосовуючи для користувача функцію.
Нехай є довгий список чисел в діапазоні $ А $ 2: $ А $ 100. Ви наклали на ці осередки умовне форматування, щоб помічені були тільки числа між 10 і 20. Тепер необхідно скласти значення в осередках, що відповідають тільки що встановленим критерієм, а потім виділити суму значень за допомогою умовного форматування. Неважливо, який саме форматування застосовується до цих осередків, проте необхідно знати критерій, згідно з яким осередки виділяються (в нашому випадку ця умова, що значення знаходиться між 10 і 20).
Щоб скласти діапазон комірок, що відповідають певним критерієм, можна використовувати функцію СУММЕСЛИ (SUMIF). але при цьому можна вказати тільки один критерій. Якщо у вас кілька факторів перевірки, можна використовувати формулу масиву. Формулу масиву можна записати так:
= SUM (IF ($ A $ 2: $ A $ 100> 10; IF ($ A $ 2: $ A $ 100<20;$A$2:$A$100)))
У російській версії Excel
= СУММ (ЯКЩО ($ А $ 2: $ А $ 100> 10; ЯКЩО ($ А $ 2: $ А $ 100<20;$А$2:$А$100)))
альтернативний шлях
В якості альтернативи можна використовувати додатковий стовпець (наприклад, стовпець В) для посилання на комірки в стовпці А. Ці посилання будуть повертати результат в стовпець В, тільки якщо значення відповідає встановленим умові, тобто> 10, <20. Для этого сделайте следующее:
Виділіть клітинку В1 і введіть наступну формулу:
= IF (AND (A2> 10; A2<20);A2;"")
У російській версії Excel
= ЕСЛИ (И (А2> 10; А2<20);А2;"")
Запишіть цю формулу в кожну клітинку до осередку В100. Після цього, якщо в стовпці А є значення, в стовпці В повинні опинитися значення між 10 і 20.
Щоб швидко скопіювати формулу в сусідні осередки вниз до останнього рядка, введіть формулу в першу клітинку (В2), заново виділіть цей осередок і двічі клацніть маркер заповнення. Це можна зробити і вибравши команду Правка → Заповнити → Вниз (Edit → Fill → Down).
Тепер виберіть будь-яку клітинку, де повинна з'явитися сума, і скористайтеся для складання стандартної функцією СУММ (SUM). (Можна приховати стовпець В, якщо ви не хочете бачити додатковий стовпець з повернутими формулою значеннями.)
Обидва попередні способи добре справляються із завданням, проте в Excel передбачена ще одна функція, яка дозволяє вказати кілька умов. Ця функція входить в набір функцій баз даних Excel і називається БДСУММ (DSUM).
Щоб перевірити її, використовуйте той же набір чисел в діапазоні А2: А100 е осередку C1: D2 і надайте цьому діапазону ім'я SumCriteria. ввівши його в поле імені зліва від рядка формул. Тепер виділіть осередок С1 і введіть = $ А $ 1. тобто посилання на першу комірку на аркуші. Скопіюйте те ж саме в клітинку D1 і ви отримаєте дві копії заголовка стовпця А. Ці копії будуть використовуватися як заголовки для умов БДСУММ (DSUM) (C1: D2). який ви назвали SumCriteria.
У комірці С2 введіть> 10. В осередку D2 введіть <20. В ячейке, где должен быть результат, введите следующий код:
= DSUM ($ А $ 1: $ А $ 100; $ А $ 1; SumCriteria)
У російській версії Excel
= БДСУММ ($ А $ 1: $ А $ 100; $ А $ 1; SumCriteria)
Функція ДСУММ (DSUM) - це кращий і самий ефективний спосіб роботи з осередками, що відповідають певним критерієм. На відміну від масивів, вбудовані функції баз даних розроблені спеціально для цієї мети, і, навіть коли вони посилаються на дуже великий діапазон і використовуються у великій кількості, їх негативний вплив на швидкість перерахунку і ефективність досить мало в порівнянні з формулами масиву.