Функция СУММЕСЛИМН в Excel: суммирование по 5 критериям сразу

Хотите быстро сложить все ячейки, выделенные желтым маркером? В Excel нет стандартной функции для этого, но решение есть — пользовательская функция VBA. Скопируйте код ниже, и сумма по цвету заливки станет доступна как обычная формула.

Шаг 1. Откройте редактор VBA

Нажмите ALT + F11 (или через меню: «Разработчик» → «Visual Basic»). Если вкладки «Разработчик» нет, добавьте её в настройках ленты.

Шаг 2. Вставьте код нового модуля

В открывшемся редакторе выберите меню Insert → Module. В появившееся окно скопируйте этот код:

Function SUMBYCOLOR(CellColor As Range, SumRange As Range) As Double
    Dim cl As Range
    Dim ColorIndex As Integer
    ColorIndex = CellColor.Interior.ColorIndex
    
    For Each cl In SumRange
        If cl.Interior.ColorIndex = ColorIndex Then
            SUMBYCOLOR = SUMBYCOLOR + cl.Value
        End If
    Next cl
End Function

Закройте редактор VBA — изменения сохраняются автоматически.

Шаг 3. Используйте новую функцию на листе

Теперь в любой ячейке вы можете писать формулу:
=SUMBYCOLOR(ячейка_с_образцом_цвета; диапазон_для_суммирования)

Например: =SUMBYCOLOR(C3; A1:A100) — просуммирует все значения в диапазоне A1:A100, заливка которых совпадает с цветом ячейки C3.

Важные ограничения и нюансы

• Только статическая заливка. Функция не работает с цветом, заданным через условное форматирование. Цвет должен быть применён вручную или макросом.
• Пересчёт. Сумма не обновится автоматически при изменении цвета ячейки. Для пересчёта нажмите F9 (пересчитать весь лист).
• Файл с поддержкой макросов. Сохраните книгу как «Книга Excel с поддержкой макросов (*.xlsm)», иначе код потеряется.

Этот приём — палочка-выручалочка для отчётов, где цветом выделены плановые/фактические показатели или категории. Работает в Excel 2007 и новее.

Нужен готовый инструмент для таких задач? Возьмите наш шаблон финансового контроллера — в нём уже встроены аналогичные функции для анализа, а все формулы защищены от случайного редактирования. Скачайте, подставьте свои данные и работайте.

#excel #формулы #vba #суммапокраске #автоматизация

Две самые обидные бытовые проблемы в Excel, которые выводят из себя даже спокойных людей, не связаны со сложными формулами.

Первая: вы листаете длинный отчет на 500 строк вниз, шапка уползает за край экрана, и вы начинаете мучительно гадать — эти 15 000 в пятой колонке — это сумма с НДС или без? Приходится скроллить наверх, сверяться и листать обратно.

Вторая: вы подготовили идеальный прайс-лист для распечатки. Нажимаете «Печать», идете к принтеру и забираете стопку бумаги, где на одном листе — таблица, а на втором — сиротливо напечатан один последний столбец. Бумага в мусорку, нервы на пределе.

Обе эти проблемы решаются за 15 секунд, если знать, куда нажимать.

Закрепляем шапку: чтобы не скроллить вслепую

Если у вас обычная таблица (сделанная руками, а не через инструмент «Умная таблица»), ее заголовки всегда будут исчезать при прокрутке. Чтобы прибить их к верхнему краю экрана намертво, используем инструмент «Закрепить области».

Самый простой способ (только для верхней строки):

1. Перейдите в верхнем меню на вкладку Вид.

2. Найдите кнопку Закрепить области.

3. В выпадающем меню выберите пункт Закрепить верхнюю строку.

4. Под первой строкой появится тонкая серая линия. Теперь можете скроллить хоть до миллионной строки — ваша шапка всегда будет перед глазами.

Способ для профи (если шапка сложная или нужно закрепить еще и столбец):

Иногда шапка занимает две-три строки, или вам нужно, чтобы при прокрутке вправо не исчезали еще и ФИО клиентов в первом столбце.

1. Встаньте в ячейку, которая находится строго под той строкой, которую нужно закрепить, и строго справа от столбца, который нужно зафиксировать. (Например, если шапка занимает строку 1, а ФИО в столбце А, кликните в ячейку B2).

2. Идите во вкладку Вид -> Закрепить области и выберите самую верхнюю кнопку — Закрепить области.

3. Готово. Экран поделен на невидимые зоны, которые не уползут из вида. Чтобы снять эту блокировку, там же нажмите «Снять закрепление областей».

Настраиваем печать: прощайте, пустые листы

Excel — программа безграничная. Она не понимает, где заканчивается лист формата А4, пока вы ей не покажете. Поэтому печатать напрямую с рабочего листа — это лотерея.

Перед тем как отправить документ на принтер, сделайте один клик: в правом нижнем углу окна Excel (возле ползунка масштаба) найдите иконку Страничный режим (третья по счету, выглядит как страница с синими полями).

Экран станет серым, а вокруг вашей таблицы появятся синие границы. Эти синие линии показывают, как таблица разрежется при печати. Пунктирная линия — это разрыв страницы.

Если пунктир отрезает ваш правый столбец, просто наведите курсор на эту пунктирную синюю линию, зажмите левую кнопку мыши и дотащите ее до сплошной синей линии на правом краю. Excel автоматически сожмет масштаб вашей таблицы так, чтобы она идеально влезла на один лист в ширину.

Как напечатать шапку на каждой странице

Еще одна боль: вы распечатали длинный договор или смету на пяти страницах. На первой странице есть заголовки колонок, а на остальных четырех — просто голые цифры. Клиенту неудобно это читать.

Сделаем так, чтобы принтер сам дублировал шапку на каждом новом листе:

1. Перейдите во вкладку Разметка страницы.

2. Найдите кнопку Печатать заголовки.

3. В появившемся окне, в поле «Сквозные строки», кликните мышкой, а затем выделите на самой таблице ту строку (или строки), где находится ваша шапка.

4. Нажмите ОК.

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

Главная иллюзия начинающего предпринимателя: «Если на расчетном счете лежат деньги, значит, бизнес прибыльный».

А потом наступает день Икс: нужно платить аренду, выдавать зарплату сотрудникам и закупать новую партию товара, а денег почему-то не хватает. Вчера же были! Начинается паника, срочные кредиты и перехватывание средств у знакомых. В бизнесе это называется страшным словом — кассовый разрыв.

Чтобы не поседеть раньше времени, бизнесу нужен контроль. И для этого на старте не обязательно покупать дорогие сложные CRM или нанимать финдиректора. Достаточно грамотно собранной таблицы в Excel.

Почему остаток на карте — это не ваша прибыль

Представьте: клиент перевел вам 500 000 рублей аванса за проект, который вы будете делать два месяца. Вы обрадовались, вытащили 100 000 рублей на личные нужды, а остальные пустили в оборот.

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

Чтобы видеть реальную картину, а не иллюзию, нужен учет. И начинать всегда стоит с отчета ДДС.

Таблица ДДС (Движение денежных средств): ваш главный инструмент

ДДС — это пульс вашего бизнеса. Это таблица, которая отвечает на два простых вопроса: «Откуда деньги пришли?» и «Куда они ушли?».

Ее суть в том, чтобы фиксировать абсолютно каждую операцию по вашим счетам, картам и наличным кассам в хронологическом порядке. Купили пачку бумаги в офис за 500 рублей? Записали. Пришла оплата от клиента на 50 000 рублей? Записали.

Правильный ДДС состоит из нескольких обязательных блоков:

• Дата операции

• Сумма (доходы с плюсом, расходы с минусом)

• Статья движения (это самое важное! Об этом ниже)

• Контрагент (от кого или кому)

• Счет (Расчетный счет, Наличка, Личная карта)

3 золотых правила ведения финансов в Excel

Если вы решили собирать свою финансовую модель в таблицах, соблюдайте три железных правила:

1. Разделите деньги бизнеса и личные деньги. Это боль 90% микробизнеса. Сегодня вы оплатили с бизнес-карты кофе, а завтра с личной карты перевели за интернет в офисе. Заведите в таблице отдельную статью расходов "Выплата дивидендов / На личные нужды учредителя". Вытаскивайте деньги системно, а не хаотично.

2. Унифицируйте справочник статей. Ваши расходы должны быть жестко категоризированы. Нельзя писать сегодня «Оплата за рекламу», завтра «Яндексу за клики», а послезавтра «Маркетологу Васе». Сделайте выпадающий список (мы писали об этом в прошлой статье!) со строгими категориями: Маркетинг, Аренда, ФОТ (Зарплата), Налоги, Закупка сырья. Иначе вы никогда не сможете собрать нормальную сводную таблицу по итогам месяца.

3. Ежедневная дисциплина. Разносить данные нужно каждый день. Или минимум раз в неделю. Если вы сядете заносить чеки за прошедший месяц 30-го числа, вы половину не вспомните, а таблица превратится в бесполезную формальность.

Как собрать первую таблицу (или где взять шаблон)

Вам нужно создать два листа в Excel. На первом листе "Справочник" выпишите все ваши источники доходов (Услуги, Розничные продажи, Опт) и статьи расходов.

На втором листе сделайте бесконечную умную таблицу (через Ctrl+T) с колонками: Дата / Статья (через выпадающий список) / Контрагент / Приход / Расход / Комментарий.

По итогам месяца вы просто выделяете эту таблицу, нажимаете «Вставка» -> «Сводная таблица» и мгновенно видите, какая статья расходов сожрала больше всего денег.

P.S. Мы знаем, что собирать структуру с нуля — это долго. Поэтому в следующих постах мы планируем выкладывать готовые автоматизированные шаблоны управленческого учета для малого бизнеса, которые можно просто скачать и начать пользоваться.