Как посчитать сумму ячеек по цвету заливки в Excel без ручного отбора

Две самые обидные бытовые проблемы в 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. Мы знаем, что собирать структуру с нуля — это долго. Поэтому в следующих постах мы планируем выкладывать готовые автоматизированные шаблоны управленческого учета для малого бизнеса, которые можно просто скачать и начать пользоваться.

Каждый, кто работает с таблицами, знает это леденящее душу чувство: вы вставили новые данные, нажали Enter, и вместо красивого отчета половина ячеек покрылась уродливыми «решетками» с непонятными аббревиатурами. Таблица выглядит так, словно на нее кто-то выругался матом.

Руководителю такой отчет не покажешь. Начинается паника: «Я всё сломал!».

Выдыхаем. Эти символы — не проклятие, а способ Excel пообщаться с вами. Программа просто говорит: «Брат, я честно пытался посчитать, но тут проблема». Давайте переведем эти жалобы на человеческий язык и научимся их лечить.

Ошибка #Н/Д (Нет Данных) — когда Excel разводит руками

Это абсолютный чемпион по частоте появлений. В 99% случаев эта ошибка вылезает, когда вы используете функцию ВПР (поиск значений из другой таблицы).

Что значит: «Ты попросил меня найти этот артикул в соседнем прайс-листе, я просмотрел всё от первой до последней строчки, но такого артикула там просто нет».

Как лечить:

• Во-первых, проверьте, действительно ли этого товара нет в прайсе. Возможно, товар просто новый.

• Во-вторых, проверьте форматы. Для Excel артикул 1234 (число) и 1234 (текст с зеленым треугольником в углу ячейки) — это разные вещи.

• В-третьих, уберите невидимые пробелы, если данные выгружались из 1С. Артикул А-123 и А-123 (с пробелом в конце) тоже не совпадут.

Ошибка #ЗНАЧ! (Значение) — попытка скрестить ежа с ужом

Excel — программа математическая. Она любит цифры. Когда вы заставляете её делать математику с текстом, у нее случается истерика.

Что значит: Вы пытаетесь сложить, умножить или поделить ячейки, в которых лежит что-то несовместимое с математикой. Например, вы написали формулу =A1+B1, где в A1 лежит 1000, а в B1 кто-то случайно написал руб или просто поставил пробел вместо нуля. Excel не умеет прибавлять рубли к цифрам математически.

Как лечить:

• Проверьте ячейки, на которые ссылается формула. Там не должно быть текста.

• Классическая боль: разные разделители. В одной ячейке написано 15,5 (через запятую), а в другой 15.5 (через точку). Запятая в русском Excel — это нормальная дробь, а точка превращает цифру в нечитаемый текст или дату. Выделите столбец, нажмите Ctrl + H (Найти и заменить) и поменяйте все точки на запятые.

Ошибка #ССЫЛКА! (Потеряшка) — удалили лишнего

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

Что значит: «В моей формуле был адрес ячейки, из которой я брал данные. Но кто-то удалил этот столбец или строку целиком! Куда мне теперь смотреть?!».

Как лечить:

• Если вы только что удалили столбец и увидели, что всё сломалось — немедленно жмите Ctrl + Z (Отмена действия).

• Если ошибку прислали вам по наследству от другого менеджера, придется зайти в формулу и заново указать ей, откуда брать правильные данные. И запомнить правило: никогда не удаляйте исходные столбцы, если на них завязаны отчеты. Лучше просто скройте их.

Изящный ремонт: маскируем уродство функцией ЕСЛИОШИБКА

Часто бывает так, что ошибка #Н/Д — это нормально. Например, у вас в таблице 1000 товаров, а на складе в наличии только 800. ВПР честно найдет 800 совпадений, а напротив 200 отсутствующих позиций выдаст #Н/Д.

Но визуально это выглядит грязно и мешает подсчитать итоговую сумму (сумма по столбцу с ошибками тоже выдаст ошибку).

Здесь нам на помощь приходит «тональный крем» для таблиц — функция ЕСЛИОШИБКА. Она оборачивает вашу основную формулу в мягкий кокон.

Синтаксис простейший: =ЕСЛИОШИБКА(ваша_длинная_формула; "что_написать_если_ошибка")

Например: =ЕСЛИОШИБКА(ВПР(A2;Прайс!A:B;2;0); 0).

Если ВПР найдет цену — он ее выведет. Если не найдет (раньше тут была бы ошибка #Н/Д) — программа спокойно поставит цифру 0. Вместо нуля можно написать текст в кавычках: "Нет на складе".

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