Книга: Компьютер для бухгалтера

Выполнение вычислений с ячейками только определенных цветов

Выполнение вычислений с ячейками только определенных цветов

Ранее уже шла речь о том, что ячейки можно форматировать по своему усмотрению, в том числе использовать для них разные цвета (см. выше раздел «Форматирование активной ячейки или диапазона»). Помимо улучшения наглядности представления информации, это можно использовать при проведении специфических бухгалтерских расчетов, в частности – когда они должны производиться на основании данных, содержащихся только в определенных ячейках, предварительно выделенных определенными цветами.

Необходимость подобных операций в бухгалтерской практике возникает часто, вот лишь несколько примеров:

• отдельное сложение сумм доходов и сумм расходов;

• раздельный учет выручки от реализации разных видов продукции;

• начисление комиссионного вознаграждения только от сумм, превышающих определенный порог;

• и т. д.

В данном разделе мы узнаем, каким образом можно решать подобные задачи, используя механизм условного форматирования.

Предположим, мы имеем длинный список какихто чисел, находящихся в диапазоне А2:А100. С помощью условного форматирования (напомним, что переход к данному механизму делается на вкладке Главная, где в разделе Стили следует нажать кнопку Условное форматирование и выбрать подходящий режим работы) были выборочно помечены только те ячейки диапазона, в которых содержатся значения от 10 до 20. Сейчас перед нами стоит задача: необходимо сложить значения во всех ячейках, которые удовлетворяют данному условию, после чего выделить полученный результат с помощью условного форматирования. В данном случае не имеет значения, какое именно форматирование применено в ячейках – нужно лишь знать точный критерий, в соответствии с которым они будут отформатированы. В рассматриваемом примере таким критерием является значение, находящееся в диапазоне от 10 до 20.

Для суммирования значений ячеек, удовлетворяющих установленному критерию, удобно использовать функцию СУММЕСЛИ, однако в данном случае возможно указание лишь одного критерия. При наличии нескольких факторов проверки целесообразнее воспользоваться формулой массива.

Ее можно записать следующим образом:

Учтите, что при вводе формул массива нужно нажимать не клавишу Enter, а комбинацию клавиш Ctrl+Shift+Enter. Это необходимо для того, чтобы программа автоматически заключила формулу в фигурные скобки.

В итоге формула должна принять следующий вид:

Существует еще один способ решения поставленной задачи. Сущность его заключается в использовании дополнительного столбца (для примера возьмем столбец В) для ссылки на ячейки, находящиеся в столбце А. Смысл применения этих ссылок состоит в том, что они отобразят результат в столбце В только в том случае, если значение будет соответствовать заранее определенному условию (в нашем примере – находиться в диапазоне от 10 до 20).

Установите курсор в ячейку В1 и введите в нее формулу следующего вида:

Затем введите данную формулу в каждую ячейку столбца В до ячейки В100 включительно.

Если столбец А у вас уже заполнен, то в столбце В вы увидите только значения, удовлетворяющие заданному условию (то есть находящиеся в диапазоне от 10 до 20).

После этого установите курсор в любую ячейку, в которой появится сумма, и примените штатную функцию Excel, предназначенную для сложения, – СУММ. При необходимости вы можете вообще скрыть столбец В, если вы не желаете иметь перед глазами возвращенные формулой результаты.

Оба рассмотренных выше способа довольно эффективно решают задачу, однако в Excel включена функция, возможности которой предусматривают применение одновременно нескольких условий (именно в этом часто нуждаются бухгалтеры, которые часто и помногу вынуждены работать с большими объемами данных).

Эта функция (она называется БДСУММ) является одной из функций баз данных. Чтобы на конкретном примере увидеть, каким образом она работает, используем тот же самый набор чисел, расположенных в диапазоне А2:А100, с которым мы уже работали ранее.

Выделите диапазон ячеек C1:D2 и присвойте ему имя SumCriteria (напомним, что имя диапазона вводится в специально предназначенном поле, которое находится слева от строки формул, см. выше раздел «Строка формул»). После этого установите курсор в ячейку С1 и введите в нее значение =$A$1 (иначе говоря, мы ввели в ячейку С1 ссылку на ячейку А1, которая является первой ячейкой рабочего листа). Теперь скопируйте данную ссылку в ячейку D1, которая находится в этом же диапазоне – в результате вы получите две копии заголовка столбца А. В дальнейшем эти созданные копии нам понадобятся в качестве заголовков условий функции БДСУММ (C1:D2) для диапазона, который мы назвали SumCriteria.

На следующем этапе в ячейку С2 следует ввести значение >10, а в ячейку D2 – значение <20. Как нетрудно догадаться, это условие, в соответствии с которым значения должны находиться в диапазоне от 10 до 20. Теперь установите курсор в ячейку, предназначенную для отображения результата, и введите в нее формулу следующего вида:

Функция БДСУММ является одним из самых эффективных приемов работы с ячейками, которые отвечают заранее установленному условию. Характерным отличием встроенных функций баз данных от массивов является то, что они были придуманы и разработаны специально для решения подобных задач и как никакой другой инструмент лучше всего для этого приспособлены. Поэтому даже тогда, когда вы работаете с внушительными объемами данных (в частности, когда этим функциям приходится ссылаться на большие диапазоны, причем одновременно используется немалое количество этих функций), это практически никак не сказывается на быстроте пересчета и на эффективности работы, чего никак нельзя сказать о формулах массива.

Оглавление книги