Бесплатный фрагмент - Электронные таблицы для финансовых и офисных задач

Введение

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

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

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

Модуль 1. Введение в Excel

1.1. MS Excel: описание и области применения

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

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

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

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

1.2. Интерфейс MS Excel

Когда вы впервые открываете Excel, перед вами появляется рабочее поле, напоминающее бесконечный лист бумаги, разделенный на клетки. Чтобы не запутаться в обилии кнопок и вкладок, стоит сразу разобраться в логике их расположения.

Верхнюю часть экрана занимает лента (1). Здесь собраны все основные инструменты, распределенные по смысловым вкладкам. Например, всё, что касается внешнего вида текста и ячеек, находится на вкладке «Главная», а инструменты для создания графиков — во вкладке «Вставка». Если лента занимает слишком много места, её легко скрыть, дважды щелкнув по любой вкладке, и она будет появляться только при нажатии.

Сразу под лентой располагается строка формул (2). Она играет роль своего рода «рентгена»: в самой ячейке мы видим результат, а в строке формул — то, как он получился. Рядом находится поле имени (3), которое подсказывает текущий адрес ячейки.

В нижней части окна расположены ярлыки листов (4). Книгу Excel удобно воспринимать как папку с документами: на одном листе может храниться общий список, на другом — детальные расчеты, а на третьем — итоговый график. Правильная организация этих зон позволяет превратить хаотичный набор цифр в стройную и понятную систему.

1.3. Адрес, содержимое, значение, формат и имя ячейки

Чтобы уверенно работать в Excel, нужно различать четыре ключевых параметра каждой клетки таблицы (ячейки).

— Адрес и имя. По умолчанию ячейка зовется по своим координатам (например, B5).

— Содержимое против значения. Это критическое различие. Содержимое — это то, что вы ввели (например, формула =10+5). Формулы в Excel вводятся со знака = (равно).

Значение — это результат, который вы видите на экране (число 15).

— Формат. Он определяет, как значение выглядит. Введем, например, число 0,5.

— Оно может отображаться и как «50%», как «0,50 тенге» или как «12:00» (полдень).

— Формат меняет только внешнюю оболочку, не затрагивая само число, участвующее в расчетах.

1.4. Работа с буфером обмена, специальная вставка

Работа в Excel начинается с ввода информации, но здесь есть свои правила. Чтобы активировать ячейку, достаточно начать печатать, а чтобы завершить ввод — нажать Enter.

Если нужно изменить уже написанное, не обязательно стирать всё содержимое. Двойной клик по ячейке или клавиша F2 позволяют войти в режим редактирования и поправить конкретный символ.

Особое внимание стоит уделить Буферу обмена. Обычное «копировать-вставить» (Ctrl+C / Ctrl+V) переносит ячейку целиком: и саму цифру, и её цвет, и формулу, которая за ней стоит. Однако в работе часто требуется более тонкий подход. Здесь на помощь приходит Специальная вставка.

Представьте, что у вас есть ячейка с формулой, которая считает итоговую премию, и она выделена жирным красным шрифтом. Вам нужно перенести только само число в другой отчет, не копируя ни формулу, ни красный цвет. Через правую кнопку мыши и меню «Специальная вставка» вы можете выбрать значения (перенесется только результат расчетов (число)) и/или форматы (перенесется только оформление (цвет, границы)).

1.5. Сортировка и фильтрация

Когда данных становится много, в игру вступают инструменты управления вниманием.

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

Например, выберем сортировку от А до Я для даты отгрузки.

Больше видов сортировок доступно в «настраиваемая сортировка» в выпадающем меню «сортировка и фильтр».

Далее рассмотрим Фильтр. Он позволяет спрятать всё лишнее. Например, из базы нам нужно быстро понять, кто из партнеров в регионах задерживает оплату. Выделяем ячейку заголовка «Статус оплаты» либо любую ячейку первой строки (заголовка). Нажимаем «Данные», далее — на значок фильтра («Фильтр»). Таблица принимает новый вид. Появились выпадающие меню для фильтрации.

Переходим в «Статус оплаты». Оставляем галочку только на пункте «Ожидание».

Результат: Excel скроет все закрытые сделки. На экране останутся только задолженности по Астане и Актобе. Это готовый список для обзвона менеджерами.

Модуль 2. Формулы и функции Excel

2.1. Относительные, абсолютные, смешанные ссылки

В Excel адреса ячеек ведут себя по-разному при копировании формул. Программа всегда старается предугадать ваши действия, и иногда это помогает, а иногда — мешает.

Относительные ссылки (по умолчанию)

Когда вы пишете простую формулу, например =B2*C2, и протягиваете её за правый нижний угол вниз, Excel воспринимает это не как «умножь конкретные ячейки», а как «умножь две клетки слева от меня».

В следующей строке формула автоматически превратится в =B3*C3, затем в =B4*C4. Это и есть относительная ссылка. Она «двигается» вместе с направлением копирования. Это идеально, когда вам нужно посчитать сумму по каждой строке в большом списке товаров.

Абсолютные ссылки (заморозка)

Но представьте другую ситуацию. У вас есть список товаров в тенге, и вам нужно пересчитать их в доллары. Курс доллара записан в одной-единственной ячейке — допустим, в E2.

Если вы напишете в первой строке =D2/E2 и протянете вниз, случится беда. В следующей строке Excel по своей привычке сдвинет обе ссылки, и получится =D3/E3. Но в ячейке E3 курса нет — там может быть пусто или написан другой текст. Расчет выдаст ошибку или ноль.

Чтобы ссылка на курс не «уползала», её нужно заморозить. Для этого используется знак доллара $.

$E$2 — это абсолютная ссылка. Теперь, куда бы вы ни копировали формулу, Excel всегда будет брать данные только из ячейки E2.

Смешанные ссылки

Бывают случаи, когда нужно «заморозить» только столбец или только строку.

$B4 — заблокирован только столбец. Если тянуть вправо, буква B не изменится.

C$3 — заблокирована только строка. Если тянуть вниз, цифра 1 останется прежней. Пример — таблица умножения:

2.2. Математические функции: СУММ, СРЗНАЧ, МИН, МАКС

Формулы в Excel позволяют выполнять отдельные расчеты, но настоящая сила программы раскрывается через функции. Функцию можно представить как готовую команду, в которую пользователь передает данные, а Excel возвращает результат.

В офисной и финансовой работе особенно часто используются базовые математические функции:

СУММ — считает общий итог;

СРЗНАЧ — находит среднее значение;

МИН — показывает наименьшее значение;

МАКС — показывает наибольшее значение;

ОКРУГЛ — округляет число до нужного количества знаков.

Эти функции кажутся простыми, но именно на них строится большинство повседневных расчетов: бюджеты, реестры платежей, отчеты по продажам, зарплатные ведомости и таблицы расходов. Функция СУММ

Функция СУММ складывает числа, отдельные ячейки и целые диапазоны. Microsoft описывает её как функцию, которая добавляет значения, диапазоны ячеек и ссылки на ячейки.

Синтаксис функции выглядит так:

=СУММ (число1; [число2]; …)

Самый простой пример:

=СУММ (B2:B5)

Эта формула складывает все числа в диапазоне от D2 до D5.

Представим таблицу доходов компании:

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

Функция СРЗНАЧ

Функция СРЗНАЧ рассчитывает среднее арифметическое значение.

Синтаксис:

=СРЗНАЧ (число1; [число2]; …)

Пример:

=СРЗНАЧ (B2:B10)

Эта формула складывает все числа в диапазоне B2:B10 и делит результат на количество числовых значений.

Представим, что компания анализирует продажи за пять дней:

Excel сложил продажи за все дни и разделит сумму на количество дней.

Среднее значение помогает понять обычный, типичный уровень показателя. Например, одна крупная продажа может резко увеличить общий итог, но среднее значение покажет более спокойную картину.

В финансах СРЗНАЧ часто используют для расчета:

— среднего чека;

— средней дневной выручки;

— средней зарплаты;

— средней цены закупки;

— среднего расхода за период.

Важно помнить: функция СРЗНАЧ учитывает только числовые значения. Пустые ячейки и текст она пропускает. Но если в ячейке стоит ноль, он участвует в расчете. Это может сильно повлиять на результат.

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

Функция МИН

Функция МИН находит наименьшее число в диапазоне.

Синтаксис:

=МИН (число1; [число2]; …)

Пример:

=МИН (B2:B10)

Представим таблицу цен от разных поставщиков:

Чтобы найти самую низкую цену, используем формулу:

=МИН (B2:B5)

Excel вернет минимальное значение — 11 900.

Эта функция удобна не только для цен. С её помощью можно быстро найти:

— минимальную продажу за период;

— самый маленький остаток товара;

— минимальную зарплату в ведомости;

— самый короткий срок поставки;

— наименьший платеж.

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

Функция МАКС

Функция МАКС работает противоположно функции МИН. Она находит наибольшее число в диапазоне.

Синтаксис:

=МАКС (число1; [число2]; …)

Пример:

=МАКС (B2:B10)

Возьмем таблицу продаж менеджеров:

Чтобы найти максимальную сумму продаж, вводим:

=МАКС (B2:B5)

Excel покажет самый высокий результат — 1 320 000.

Функция МАКС помогает быстро найти:

— самую крупную сделку;

— максимальную задолженность клиента;

— самый высокий расход за месяц;

— наибольший остаток товара;

— максимальную зарплату или премию.

В отчетах МАКС часто используют для поиска отклонений. Например, если один расход резко выше остальных, это может быть нормальной крупной закупкой, а может быть ошибкой ввода. Функция помогает быстро обратить внимание на такие значения.

2.3. Статистический анализ: СЧЁТ, СЧЁТЗ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ

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

Например:

— сколько платежей уже внесено;

— сколько клиентов указано в базе;

— сколько заявок находится в статусе «Ожидание»;

— какая средняя сумма продаж только по одному менеджеру.

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

В данном подразделе рассмотрим четыре функции:

СЧЁТ — считает количество числовых значений;

СЧЁТЗ — считает количество непустых ячеек;

СЧЁТЕСЛИ — считает количество ячеек по условию;

СРЗНАЧЕСЛИ — рассчитывает среднее значение по условию.

Эти функции особенно полезны при работе с реестрами, анкетами, списками оплат, базами клиентов и отчетами по продажам.

Функция СЧЁТ

Функция СЧЁТ подсчитывает, сколько числовых значений находится в диапазоне.

Синтаксис:

=СЧЁТ (значение1; [значение2]; …)

На практике чаще всего указывают диапазон:

=СЧЁТ (B2:B10)

Эта формула проверяет диапазон B2:B10 и считает только те ячейки, где находятся числа.

Представим таблицу оплат:

Если нужно узнать, сколько клиентов уже внесли оплату, можно посчитать количество чисел в столбце «Сумма оплаты»:

=СЧЁТ (C2:C6)

Excel вернет результат 3, потому что сумма оплаты указана только в трех строках.

Функция СЧЁТ удобна, когда нужно посчитать:

— количество платежей;

— количество заполненных числовых показателей;

— число выставленных счетов с суммой;

— количество дней, по которым есть выручка;

— количество оценок, баллов или количественных данных.

Важно помнить: СЧЁТ не считает текст. Если в ячейке написано «Оплачено», «Нет данных» или имя клиента, функция не включит такую ячейку в результат.

Функция СЧЁТЗ

Функция СЧЁТЗ считает количество непустых ячеек. В отличие от СЧЁТ, она учитывает не только числа, но и текст, даты, формулы и другие значения.

Синтаксис:

=СЧЁТЗ (значение1; [значение2]; …)

Пример:

=СЧЁТЗ (A2:A10)

Эта формула считает, сколько ячеек в диапазоне A2:A10 заполнено хотя бы чем-то.

Возьмем список клиентов:

Если нужно посчитать, сколько клиентов внесено в базу, можно использовать:

=СЧЁТЗ (B2:B6)

Excel вернет 4, потому что в столбце «Клиент» заполнены четыре ячейки.

Если нужно узнать, у скольких клиентов указан телефон:

=СЧЁТЗ (C2:C6)

Результат будет 3.

Функция СЧЁТЗ полезна для контроля заполненности таблицы. С её помощью можно быстро понять, сколько строк действительно заполнено, а где данные отсутствуют.

Её часто используют для подсчета:

— количества клиентов в базе;

— количества заполненных заявок;

— количества сотрудников в списке;

— количества договоров;

— количества заполненных полей анкеты.

Главное отличие:

=СЧЁТ (C2:C6) считает только числа;

=СЧЁТЗ (C2:C6) считает все непустые ячейки.

Например, если в ячейке написано «Нет данных», функция СЧЁТ её не посчитает, а СЧЁТЗ — посчитает.

Функция СЧЁТЕСЛИ

Функция СЧЁТЕСЛИ считает количество ячеек, которые соответствуют заданному условию.

Синтаксис:

=СЧЁТЕСЛИ (диапазон; условие)

Первый аргумент — где искать.

Второй аргумент — что искать.

Например:

=СЧЁТЕСЛИ (B2:B10; «Астана»)

Эта формула считает, сколько раз в диапазоне B2:B10 встречается значение «Астана».

Представим таблицу заявок:

Чтобы узнать, сколько заявок относится к Астане, используем:

=СЧЁТЕСЛИ (B2:B6; «Астана»)

Результат — 3.

Чтобы узнать, сколько заявок ожидают оплату:

=СЧЁТЕСЛИ (C2:C6; «Ожидание»)

Результат — 2.

СЧЁТЕСЛИ часто применяется в офисной работе для подсчета:

— количества оплаченных счетов;

— количества просроченных задач;

— числа клиентов из определенного города;

— количества сотрудников конкретного отдела;

— количества заявок в нужном статусе.

Условия с числами

СЧЁТЕСЛИ может работать не только с текстом, но и с числами.

Например, есть список сумм продаж:

Чтобы посчитать количество сделок больше 200 000, используется формула:

=СЧЁТЕСЛИ (B2:B6; "> 200000»)

Результат — 2.

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

«> 200000»

Можно использовать разные условия:

«> 100000» — больше 100 000;

«<50000» — меньше 50 000;

«=0» — равно нулю;

«<> 0» — не равно нулю.

Если пороговое значение записано в отдельной ячейке, например в E2, формулу лучше записать так:

=СЧЁТЕСЛИ (B2:B6; ">"&E2)

Здесь знак & соединяет текстовый оператор ">" и значение из ячейки E2.

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

Функция СРЗНАЧЕСЛИ

Функция СРЗНАЧЕСЛИ рассчитывает среднее значение только для тех строк, которые соответствуют условию.

Синтаксис:

=СРЗНАЧЕСЛИ (диапазон; условие; [диапазон_усреднения])

Первый аргумент — где проверяется условие.

Второй аргумент — само условие.

Третий аргумент — какие числа нужно усреднять.

Если третий аргумент не указан, Excel будет усреднять тот же диапазон, в котором проверяется условие.

Рассмотрим таблицу продаж:

Чтобы найти среднюю сумму продажи по Астане, используем:

=СРЗНАЧЕСЛИ (A2:A6; «Астана»; C2:C6)

Excel проверит города в диапазоне A2:A6, выберет строки, где указана Астана, и рассчитает среднее значение по соответствующим суммам из диапазона C2:C6.

В нашем примере будут учтены три продажи:

250 000;

320 000;

150 000.

Среднее значение:

(250 000 +320 000 +150 000) / 3 = 240 000

Функция СРЗНАЧЕСЛИ полезна, когда нужно узнать:

— среднюю продажу по одному городу;

— средний чек по конкретному менеджеру;

— среднюю зарплату в одном отделе;

— средний расход по выбранной категории;

— среднюю оценку по определенной группе.

2.4. Бизнес-логика: ЕСЛИ, ЕСЛИМН, ЕСЛИОШИБКА

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

Например:

— платеж просрочен или нет;

— план продаж выполнен или не выполнен;

— клиент получает скидку или платит полную стоимость;

— сотруднику начисляется премия или нет;

— ошибка в формуле должна отображаться на экране или заменяться понятным текстом.

Для таких задач используются логические функции. Они позволяют Excel действовать по принципу:

если условие выполняется — сделать одно, если не выполняется — сделать другое.

В этом разделе рассмотрим три функции:

ЕСЛИ — проверяет одно условие;

ЕСЛИМН — проверяет несколько условий по порядку;

ЕСЛИОШИБКА — заменяет ошибку в формуле на понятный результат.

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

Функция ЕСЛИ

Функция ЕСЛИ проверяет условие и возвращает один результат, если условие истинно, и другой результат, если условие ложно.

Синтаксис:

=ЕСЛИ (логическое_выражение; значение_если_истина; значение_если_ложь)

Разберем эту запись по частям.

Логическое_выражение — это условие, которое Excel должен проверить.

Значение_если_истина — что показать, если условие выполняется.

Значение_если_ложь — что показать, если условие не выполняется.

Простой пример:

=ЕСЛИ (B2> =100000; «План выполнен»; «План не выполнен»)

Эта формула проверяет значение в ячейке B2. Если оно больше или равно 100 000, Excel покажет текст «План выполнен». Если меньше — «План не выполнен».

Пример: проверка выполнения плана

Представим таблицу продаж менеджеров:

В столбце «Статус» нужно показать, выполнил ли менеджер план.

В ячейке D2 можно написать:

=ЕСЛИ (C2> =B2; «План выполнен»; «План не выполнен»)

Excel сравнит факт продаж с планом.

Если факт больше или равен плану, появится «План выполнен».

Если факт меньше плана, появится «План не выполнен».

После этого формулу можно протянуть вниз на остальные строки.

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

Условия сравнения

В функции ЕСЛИ часто используются знаки сравнения:

> — больше;

< — меньше;

> = — больше или равно;

<= — меньше или равно;

= — равно;

<> — не равно.

Примеры:

=ЕСЛИ (C2> 0; «Есть продажи»; «Нет продаж»)

=ЕСЛИ (D2=«Оплачено»; «Закрыто»; «Проверить»)

=ЕСЛИ (B2 <> «»; «Заполнено»; «Пусто»)

Последний пример проверяет, заполнена ли ячейка B2. Запись «» означает пустую строку. Если B2 не равно пустоте, Excel покажет «Заполнено», иначе — «Пусто».

Пример: расчет премии

Функция ЕСЛИ может возвращать не только текст, но и числа или результаты расчетов.

Представим, что сотрудник получает премию 5% от продаж, если выполнил план:

В ячейке D2 можно написать:

=ЕСЛИ (C2> =B2; C2*5%; 0)

Если менеджер выполнил план, Excel умножит фактические продажи на 5%.

Если не выполнил — поставит 0.

Для Иванова премия будет рассчитана, потому что 1 150 000 больше плана.

Для Садыковой результат будет 0, потому что план не выполнен.

Такой принцип часто используется в расчетах бонусов, скидок, штрафов и комиссий.

Вложенные функции ЕСЛИ

Иногда одного условия недостаточно. Например, нужно присвоить рейтинг в зависимости от процента выполнения плана:

— 100% и выше — «Отлично»;

— от 80% до 99% — «Нормально»;

— ниже 80% — «Плохо».

Такую задачу можно решить вложенными функциями ЕСЛИ:

=ЕСЛИ (C2/B2> =1; «Отлично»; ЕСЛИ (C2/B2> =0,8; «Нормально»; «Плохо»))

Excel сначала проверяет первое условие: факт делится на план. Если результат больше или равен 1, выводится «Отлично».

Если первое условие не выполнено, Excel переходит ко второй проверке. Если выполнение плана больше или равно 80%, выводится «Нормально». Если и это условие не выполнено, выводится «Плохо».

Такая формула работает, но читать её сложнее. Чем больше условий, тем длиннее становится запись. Поэтому для нескольких вариантов удобнее использовать функцию ЕСЛИМН.

Функция ЕСЛИМН

Функция ЕСЛИМН проверяет несколько условий по порядку и возвращает результат для первого выполненного условия.

Синтаксис:

=ЕСЛИМН (условие1; значение1; условие2; значение2; …)

Формула читается так:

если условие1 выполняется — вернуть значение1;

если условие2 выполняется — вернуть значение2;

и так далее.

Перепишем пример с рейтингом через ЕСЛИМН:

=ЕСЛИМН (C2/B2> =1; «Отлично»; C2/B2> =0,8; «Нормально»; C2/B2 <0,8; «Плохо»)

Такая запись обычно понятнее, чем вложенные функции ЕСЛИ:

Excel проверяет условия слева направо. Как только он находит первое истинное условие, он возвращает соответствующий результат и дальше не проверяет.

Поэтому порядок условий очень важен.

Важность порядка условий

Представим, что мы хотим присвоить уровень скидки по сумме заказа:

— от 500 000 и выше — скидка 10%;

— от 300 000 и выше — скидка 7%;

— от 100 000 и выше — скидка 3%;

— меньше 100 000 — скидка 0%.

Правильная формула:

=ЕСЛИМН (B2> =500000; 10%; B2> =300000; 7%; B2> =100000; 3%; B2 <100000; 0%)

Здесь условия идут от большего к меньшему. Если заказ равен 600 000, Excel сразу назначит скидку 10%.

А теперь неправильный порядок:

=ЕСЛИМН (B2> =100000; 3%; B2> =300000; 7%; B2> =500000; 10%; B2 <100000; 0%)

Если сумма заказа равна 600 000, первое условие B2> =100000 уже выполняется. Excel вернет 3% и до условий 7% и 10% уже не дойдет.

Поэтому в ЕСЛИМН условия нужно располагать так, чтобы более строгие условия проверялись раньше.

Пример: классификация задолженности

Функция ЕСЛИМН удобна для финансового контроля. Например, нужно разделить клиентов по сроку просрочки:

— 0 дней — «Нет просрочки»;

— от 1 до 10 дней — «Небольшая просрочка»;

— от 11 до 30 дней — «Средний риск»;

— более 30 дней — «Высокий риск».

В ячейке C2 можно написать:

=ЕСЛИМН (B2=0; «Нет просрочки»; B2 <=10; «Небольшая просрочка»; B2 <=30; «Средний риск»; B2> 30; «Высокий риск»)

Здесь условия идут от меньшего срока к большему. Если просрочка 5 дней, Excel вернет «Небольшая просрочка». Если 18 дней — «Средний риск». Если 45 дней — «Высокий риск».

Такая классификация помогает быстро расставить приоритеты: кого нужно просто напомнить об оплате, а с кем уже стоит работать внимательнее.

Функция ЕСЛИОШИБКА

В реальных таблицах формулы не всегда дают красивый результат. Иногда Excel показывает ошибки:

#ДЕЛ/0! — деление на ноль;

#Н/Д — значение не найдено;

#ЗНАЧ! — неправильный тип данных;

#ССЫЛКА! — ссылка повреждена;

#ИМЯ? — Excel не распознал имя функции или диапазона.

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

Функция ЕСЛИОШИБКА позволяет заменить ошибку на понятный текст, ноль или пустую ячейку.

Синтаксис:

=ЕСЛИОШИБКА (значение; значение_если_ошибка)

Первый аргумент — формула, которую нужно проверить.

Второй аргумент — что показать, если формула вернула ошибку.

Пример: деление на ноль

Представим таблицу расчета средней цены:

Обычная формула средней цены:

=B2/C2

Для первой строки всё работает: 700 000 делится на 2.

Но во второй строке количество равно 0. Делить на ноль нельзя, поэтому Excel покажет ошибку #ДЕЛ/0!.

Чтобы отчет выглядел аккуратно, можно использовать:

=ЕСЛИОШИБКА (B2/C2; 0)

Теперь, если расчет невозможен, Excel покажет 0.

Иногда вместо нуля лучше показать текст:

=ЕСЛИОШИБКА (B2/C2; «Нет данных»)

А иногда лучше оставить ячейку визуально пустой:

=ЕСЛИОШИБКА (B2/C2; «»)

Запись «» означает пустой текст.

Важно понимать: ЕСЛИОШИБКА делает отчет аккуратнее, но не исправляет исходные данные.

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

2.5. Хронологический учет: функции даты и времени

В финансовых таблицах время играет такую же важную роль, как сумма или процент. Платеж может быть внесен вовремя или с задержкой. Счет может быть выставлен сегодня, а оплачен через 10 рабочих дней. Кредитный платеж может повторяться каждый месяц. Отчет может строиться на основе текущей даты.

Поэтому в Excel важно уметь работать не только с числами, но и с датами.

На первый взгляд дата выглядит как обычный текст:

15.01.2026

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

В данном подразделе рассмотрим базовые функции даты и времени:

СЕГОДНЯ — показывает текущую дату;

ДАТА — собирает дату из года, месяца и дня;

ДЕНЬ, МЕСЯЦ, ГОД — извлекают части даты;

ДАТАМЕС — прибавляет или вычитает месяцы;

РАБДЕНЬ — рассчитывает дату через заданное количество рабочих дней.

Эти функции особенно полезны при работе с договорами, счетами, графиками платежей, сроками поставки и контролем просроченной задолженности.

Как Excel понимает дату

Дата в Excel — это не просто надпись. Программа воспринимает её как порядковый номер дня.

Например, если в ячейку ввести дату и изменить формат ячейки на числовой, Excel покажет не привычную дату, а число. Это число используется для расчетов.

Именно поэтому можно написать:

=B2-A2

Если в A2 указана дата выставления счета, а в B2 — дата оплаты, Excel посчитает количество дней между ними.

Пример:

В ячейке D2 можно написать:

=C2-B2

Excel покажет, сколько дней прошло между выставлением счета и оплатой.

Если счет выставлен 05.01.2026, а оплачен 12.01.2026, результат будет 7 дней.

Такой расчет часто используется для анализа платежной дисциплины клиентов.

Функция СЕГОДНЯ

Функция СЕГОДНЯ возвращает текущую дату.

Синтаксис:

=СЕГОДНЯ ()

У этой функции нет аргументов, поэтому скобки остаются пустыми.

Если сегодня 29.06.2026, формула покажет:

29.06.2026

Главная особенность функции СЕГОДНЯ в том, что она обновляется автоматически. Если открыть файл завтра, дата изменится на завтрашнюю.

Эта функция полезна, когда нужно сравнить дату в таблице с текущей датой.

Пример: контроль просроченной оплаты

Представим таблицу счетов:

Нужно определить, какие счета уже просрочены.

В ячейке C2 можно написать:

=ЕСЛИ (B2 <СЕГОДНЯ (); «Просрочено»; «В срок»)

Формула сравнивает срок оплаты с текущей датой.

Если срок оплаты меньше сегодняшней даты, значит платеж просрочен.

Если срок оплаты равен сегодняшней дате или позже, Excel покажет «В срок».

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

Функция ДАТА

Функция ДАТА создает дату из трех частей: года, месяца и дня.

Синтаксис:

=ДАТА (год; месяц; день)

Пример:

=ДАТА (2026; 1; 15)

Результат:

15.01.2026

На первый взгляд может показаться, что проще просто ввести дату вручную. Но функция ДАТА полезна, когда год, месяц и день находятся в разных ячейках или рассчитываются формулой.

Например:

В ячейке D2 можно написать:

=ДАТА (A2; B2; C2)

Excel соберет корректную дату из отдельных частей.

Это удобно при формировании графиков платежей, отчетных периодов и календарных планов.

Функции ДЕНЬ, МЕСЯЦ и ГОД

Иногда нужно выполнить обратное действие: не собрать дату, а извлечь из неё отдельную часть.

Для этого используются функции:

=ДЕНЬ (дата)

=МЕСЯЦ (дата)

=ГОД (дата)

Пример:

Если дата находится в ячейке A2, можно использовать формулы:

=ДЕНЬ (A2)

=МЕСЯЦ (A2)

=ГОД (A2)

Для даты 15.01.2026 Excel вернет:

день — 15;

месяц — 1;

год — 2026.

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

Например, если в реестре платежей есть столбец «Дата оплаты», можно добавить отдельный столбец «Месяц» и использовать:

=МЕСЯЦ (A2)

После этого таблицу будет проще фильтровать и анализировать по месяцам.

Функция ДАТАМЕС

Функция ДАТАМЕС возвращает дату, которая находится на заданное количество месяцев раньше или позже исходной даты.

Синтаксис:

=ДАТАМЕС (начальная_дата; число_месяцев)

Пример:

=ДАТАМЕС (A2; 1)

Эта формула прибавляет к дате из A2 один месяц.

Если в A2 указано 15.01.2026, результат будет:

15.02.2026

Если нужно вычесть месяц, используется отрицательное число:

=ДАТАМЕС (A2; -1)

Для даты 15.01.2026 результат будет:

15.12.2025

Функция ДАТАМЕС особенно полезна для регулярных платежей: аренды, кредита, подписки, рассрочки или абонентской платы.

Пример: график ежемесячных платежей

Представим, что компания арендует офис и должна платить каждый месяц. Первый платеж — 10.01.2026.

В ячейке B3 можно написать:

=ДАТАМЕС (B2; 1)

Затем формулу можно протянуть вниз. В формате числа выбираем «Дата».

Excel автоматически построит последовательность дат:

10.02.2026;

10.03.2026;

10.04.2026;

10.05.2026.

Такой прием часто используется при создании графиков платежей по аренде, кредиту или рассрочке.

ДАТАМЕС и конец месяца

Функция ДАТАМЕС особенно полезна потому, что корректно работает с разной длиной месяцев.

Например, если начальная дата — 31.01.2026, а мы прибавляем один месяц:

=ДАТАМЕС (A2; 1)

Excel вернет последний возможный день февраля:

28.02.2026

Это логично: в феврале нет 31-го числа.

Такое поведение важно при составлении графиков платежей, где срок оплаты установлен

Функция РАБДЕНЬ

В реальной работе сроки часто считаются не в календарных, а в рабочих днях. Например, договор может предусматривать оплату в течение 5 рабочих дней после выставления счета. В этом случае выходные дни не должны учитываться.

Для таких задач используется функция РАБДЕНЬ.

Синтаксис:

=РАБДЕНЬ (начальная_дата; количество_дней; [праздники])

Первый аргумент — дата, от которой начинается расчет.

Второй аргумент — количество рабочих дней.

Третий аргумент — необязательный список праздничных дат.

Пример:

=РАБДЕНЬ (A2; 5)

Эта формула прибавляет к дате из A2 пять рабочих дней, пропуская субботу и воскресенье.

Пример: срок оплаты по счету

Представим, что счет выставлен 01.07.2026, а по договору клиент должен оплатить его в течение 5 рабочих дней.

В ячейке D2 можно написать:

=РАБДЕНЬ (B2; C2)

Excel рассчитает дату оплаты с учетом рабочих дней.

Если дата выставления счета приходится на среду, то 5 рабочих дней будут считаться без субботы и воскресенья. Это делает расчет ближе к реальным условиям договора.

Учет праздничных дней

В некоторых расчетах недостаточно исключить только субботу и воскресенье. Нужно также учитывать праздничные дни.

Для этого праздники можно записать в отдельном диапазоне, например в H2:H10:

Тогда формула будет выглядеть так:

=РАБДЕНЬ (B2; C2; $H$2:$H$10)

Здесь диапазон праздников лучше сделать абсолютной ссылкой с помощью знаков $, чтобы при копировании формулы он не смещался.

Такой расчет полезен при планировании оплат, поставок, сроков обработки заявок и подготовки документов.

Модуль 3. Работа с финансовыми базами данных

3.1. Умные таблицы (Ctrl+T): динамические реестры и автоматические расчеты

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