Всем доброго дня. Сегодня хочу предложить вашему вниманию решение одной реальной задачи, которую мне прислала слушательница. Сразу отмечу, что похожие вопросы были и ранее. Поэтому решил аж целую статью накатать по этому поводу :)
Примечание: целью данной статьи ни в коем не является высмеивание людей, которые по тем или иным причинам пишут огромные формулы. Я лишь хочу показать, как важно знать различные мелкие нюансы, чтобы не усложнять себе жизнь на ровном месте.
Формульный детокс: как сократить Excel-формулу размером с простыню до одной строки
Если не все, то наверняка многие сталкивались когда-нибудь с чем-то похожим:
Открываешь ячейку, а там… формула длиной в три экрана, собранная из костылей, велосипедов и какой-то матери. Если вы думаете, что это я специально написал в горячечном бреду, то нет. Самое забавное, что ЭТО даже как-то работало. И так было. Пока не появились они - счастливые обладатели компьютеров Apple. У них почему-то это не работало. Но это было не самое важное (не в обиду пользователям Apple). Куда важнее было оптимизировать формулу до адекватных размеров. Именно с таким вопросом ко мне и обратились. И сейчас я хочу представить вашему вниманию алгоритм решения похожих задач.
СПОЙЛЕР: задача была решена.
Шаг 1: определяем цель.
Прежде чем досконально разбираться в самой формуле (если честно, я этого так и не сделал), мне нужно было понять, какую вообще цель мы преследуем. Всё оказалось очень просто. Формула должна была решать тривиальную задачу: брать стоимость товара в долларах и переводить её в рубли по курсу ЦБ на определенную дату отгрузки. Казалось бы, обычный ВПР (VLOOKUP). Но автор исходника столкнулся с проблемой: курс есть на рабочие дни. На субботу и воскресенье банк его не выкатывает — нужно брать значение за пятницу. Чтобы это обойти, в формулу зашили кучу условий ЕСЛИ (IF), текстовые функции СЦЕПИТЬ (CONCATENATE), вытаскивание по отдельности дней, месяцев и годов... В общем, получился кошмар, который к тому же не работал на MacOS. Скорее всего, из-за разных стандартов в системах. Конкретную причину я не выяснял, так как после того, как отправил свой вариант, слушательница написала, что теперь всё работает даже на маках.
Шаг 2: подбираем инструменты.
После того, как наметили цель, нужно понять, что нам поможет в достижении этой цели. Так, данные нужно подтянуть из одной таблицы в другую. Всё просто: ВПР (VLOOKUP). Движемся дальше.
Шаг 3: дьявол кроется в деталях.
И вот теперь переходим к самому интересному. Если задача такая простая, почему формула-то такая сложная?! Предлагаю вам взглянуть на таблицу с датами и попробовать самим понять, что с ними не так:
Посмотрели? Поняли? Кто понял, тот молодец, кто не понял, но продолжит читать, тот тоже молодец.
А царь-то даты-то ненастоящие! Выровнены по левому краю. Значит, это текст. И нельзя с ними работать, как с нормальными датами. Именно по этой причине формула и была такой страшной. Excel не умеет сопоставлять "правильную" дату из вашего отчета с текстовой строкой "13.07.2026". Автор извлекал по отдельности день, месяц, год. Далее с помощью функции ТЕКСТ(FORMAT) преобразовывал всё это в дату, и только потом сравнивал с датой в основной таблице. Вот этот кусок:
Решение очевидное: преобразовать текстовые даты в корректные даты. Тут стоит отметить, что нельзя это сделать простым изменением формата. Нельзя и всё тут. Далее те самые 5 стадий: отрицание, гнев, торг, депрессия, принятие. И теперь решение. В общем, есть безумно простой способ сделать это. Выделяем столбец, нажимаем Ctrl + H (Поиск и замена). В поле "Найти" пишем точку "." и в поле «Заменить на» пишем тоже точку ".". Жмем "Заменить все". Excel принудительно перезапишет ячейки, поймет, что это числа, и выровняет их по правому краю. Даты вылечены! Если в датах у вас дефисы или слэшы (/), то заменяйте их.
Теперь, когда даты стали датами, выбрасываем 90% старой формулы. Нам нужен ВПР. Но как заставить его автоматически брать курс за пятницу, если дата отгрузки выпала на субботу или воскресенье?
Все привыкли писать в конце ВПР ноль (0 или ЛОЖЬ), требуя точного совпадения. Но если мы поставим туда 1 или ИСТИНА, включится приблизительное совпадение.
Как это работает: если Excel не находит в таблице точную дату (например, субботу), он автоматически берет ближайшее меньшее значение. А ближайшим меньшим значением для субботы и воскресенья в календаре как раз и будет пятница!
!!! Важнейший нюанс !!!: чтобы этот трюк сработал, таблица с курсами валют ОБЯЗАТЕЛЬНО должна быть отсортирована по возрастанию (от старых дат к новым). Иначе ВПР сойдет с ума.
О чудо: огромная простыня текста превратилась в изящное:
Бонус 1: для зажиточных бояр.
Если у вас Excel 2021 года или свежее, можно использовать преемника — функцию ПРОСМОТРX (XLOOKUP). Она еще круче, потому что ей плевать на сортировку. Даже если даты в базе идут в полном хаосе, вы просто выставляете в пятом аргументе режим сопоставления -1 (точное совпадение или следующее меньшее). И всё работает идеально.
Бонус 2: для всех.
Что делать, если вам всё-таки нужно разобраться в чужой сложной формуле, а она выдает ошибку? Не пытайтесь расшифровать её глазами.
В Excel есть встроенный отладчик. Встаньте на ячейку со страшной формулой, перейдите на вкладку "Формулы" - группа "Зависимости формул" - кнопка "Вычислить формулу" (иконка с лупой и fx).
Excel откроет окно, в котором можно посмотреть, что происходит в формуле. Нажимая кнопку "Вычислить", вы будете видеть, как постепенно, шаг за шагом, считаются внутренние функции. Вы сразу (или не сразу, если формула сложно-огромная) поймете, на каком именно этапе и почему формула спотыкается и выдает ошибку.
Резюме:
У самурая нет цели, есть только путь — но в Excel этот подход не работает. Прежде чем писать гигантские формулы:
Приведите данные к правильному формату.
Определитесь с целью.
Подберите инструменты и изучите их.
Стоит отметить, что сократить формулу можно не всегда. Порой логика сложная и формула должна быть соответствующей. Тут уже ничего не попишешь. Либо смотреть в сторону Power Query, Pivot или VBA.
На этом, пожалуй, всё. Спасибо всем, кто дочитал до конца. Надеюсь, было полезно. В комментариях делитесь своими формулами-монстрами. Если статья "зайдёт", то, думаю, будет ещё несколько похожих, так как за время преподавания уже накопился определённый материал.
Когда мы разрабатываем систему, чаще всего тестируем её на небольшом объёме данных: несколько десятков или сотен записей. Но в реальной эксплуатации база может содержать миллионы строк, а одновременно системой могут пользоваться тысячи пользователей.
Именно поэтому перед вводом системы в эксплуатацию проводят нагрузочное тестирование.
В канале Аналитика FM я публикую реальные запросы SQL с разбором логики. Обсуждаем продуктовые метрики и как правильно строить аналитику данных. Канал веду с нуля подписчиков. Подписывайся, если тоже хочешь вникнуть в прекрасный мир аналитики.
Нагрузочное тестирование - это проверка работы системы под нагрузкой, максимально приближенной к реальной.
Его цель - ответить на вопросы:
Сколько пользователей одновременно выдержит система?
Как быстро будут выполняться запросы?
Как изменится производительность при увеличении объёма данных?
Не начнёт ли база данных работать значительно медленнее?
Не возникнут ли ошибки из-за нехватки памяти, места во временном табличном пространстве или перегрузки процессора?
Например, запрос, который за 0,1 секунды обрабатывает 100 строк, может выполняться несколько минут при таблице в 100 миллионов записей.
Поэтому проверять производительность необходимо именно на объёмах, близких к реальным.
Какие бывают виды нагрузочного тестирования?
1. Load Testing (нагрузочное тестирование)
Проверяется работа системы при обычной ожидаемой нагрузке.
Например:
одновременно работают 500 пользователей;
таблица содержит 10 миллионов записей.
2. Stress Testing (стресс-тестирование)
На систему специально подают нагрузку выше расчётной.
Цель - определить предел её возможностей и понять, как она поведёт себя при перегрузке.
3. Volume Testing (тестирование объёма данных)
Проверяется влияние количества данных на производительность.
Например:
запрос работает с таблицей из 100 тыс. строк;
затем с 10 млн;
затем со 100 млн.
4. Endurance Testing (тестирование на длительную работу)
Система работает под постоянной нагрузкой несколько часов или даже суток.
Проверяют, не возникает ли утечек памяти и деградации производительности.
Как подготовить данные для нагрузочного тестирования Oracle?
Самый простой способ - сгенерировать тестовые записи прямо средствами Oracle.
Например:
SELECT 'ООО Ромашка ' || LEVEL AS name,
7700000000 + LEVEL AS inn,
1027700000000 + LEVEL AS ogrn
FROM dual
CONNECT BY LEVEL <= 1000000;
Этот запрос создаст 1 миллион строк без использования каких-либо таблиц.
И так вы сможете подготовить базу с наполненными данными для тестирования нагрузки.
Иногда такого подхода не хватает, и данные должны быть консистентными, иметь логическую зависимость от других параметров. Но это уже более глубокая семантика нагрузочного тестирования.
А мы с вами начинаем познавать увлекательный мир аналитики и данных.
Разбор скрипта проведем в канале Аналитика FM. Подписывайся, чтобы узнавать техническую сторону работы аналитика.
Генерация счетов-фактур — одна из самых распространённых задач в бизнес-приложениях. Будь то разработка ERP-системы, платформы электронной коммерции или внутреннего инструмента для выставления счетов, ручное создание счетов быстро становится неэффективным по мере роста числа клиентов.
Распространённый подход — разрабатывать счета в Excel, программно заполнять их данными, а затем экспортировать в PDF-файлы для распространения. Это сочетает гибкость шаблонов Excel с переносимостью и профессиональным видом PDF-документов.
В этой статье вы узнаете, как построить автоматизированный процесс создания счетов с помощью библиотеки Free Spire.XLS для Python. Мы возьмём шаблон счёта в Excel, заполним его информацией о клиенте и товарах, а затем экспортируем готовый счёт напрямую в PDF.
Почему стоит использовать шаблоны Excel для счетов?
Многие разработчики пытаются генерировать счета непосредственно в PDF. Хотя это работает, часто требует построения макетов, форматирования таблиц, позиционирования текста и расчёта итогов в коде.
Шаблоны Excel имеют ряд преимуществ:
Просты в изменении для бизнес-пользователей
Встроенная поддержка формул и вычислений
Привычная среда проектирования
Не нужно жёстко прописывать макеты в коде
Простой экспорт в PDF
Вместо того чтобы воссоздавать макет счёта в коде, вы можете позволить Excel заниматься форматированием, в то время как ваше приложение сосредоточится на предоставлении данных.
Процесс выглядит так:
Данные счёта
↓
Шаблон Excel
↓
Заполнение ячеек
↓
Пересчёт формул
↓
Экспорт в PDF
Проектирование шаблона счёта
Наш шаблон счёта содержит три раздела:
Верхняя часть счёта
Верхняя часть включает информацию о компании и реквизиты счёта.
Эти ячейки будут заполняться программно.
Позиции счёта
Раздел позиций начинается со строки 16 и реализован как таблица Excel .
Этот выбор важен, поскольку таблицы Excel предоставляют несколько встроенных возможностей:
Автоматическое чередование цветов строк
Автоматическое распространение формул
Динамическое расширение таблицы
Упрощённое обслуживание
Например:
Шаблон уже содержит формулы для расчёта итогов по строкам.
Поскольку в шаблоне уже есть формулы для столбца «Line Total», нет необходимости вычислять суммы в коде.
Excel выполняет вычисления автоматически.
Почему стоит использовать таблицу Excel для позиций счёта?
Одна из самых больших проблем при генерации счетов — обработка переменного количества товаров.
Традиционные шаблоны часто требуют от разработчиков:
Вручную копировать формулы
Дублировать форматирование
Пересчитывать диапазоны
Обновлять итоги
Таблицы Excel устраняют большую часть этой сложности.
При вставке новых строк:
Чередование цветов строк сохраняется
Формулы автоматически распространяются на новые строки
Ссылки на таблицу обновляются автоматически
Итоги продолжают работать корректно
В результате приложению нужно только сосредоточиться на вставке данных.
Такое разделение обязанностей делает код гораздо чище и проще в обслуживании.
Обработка более шести позиций счёта
Наш шаблон резервирует шесть строк для товаров.
Если счёт содержит более шести позиций, можно динамически добавить дополнительные строки.
base_rows = 6
if len(invoice["items"]) > base_rows:
sheet.InsertRow(
22,
len(invoice["items"]) - base_rows
)
Поскольку список товаров реализован как таблица Excel, новые вставленные строки автоматически наследуют форматирование и формулы таблицы.
Никакого дополнительного кода для стилизации не требуется.
Пересчёт формул
Перед экспортом пересчитайте все формулы в книге.
workbook.CalculateAllValue()
Это гарантирует, что итоги по строкам, промежуточные итоги, налоги и общие итоги будут отражать актуальные данные.
Управление макетом страницы и масштабированием PDF
Перед экспортом счёта в PDF часто стоит настроить параметры страницы рабочего листа.
Без правильной конфигурации в сгенерированном PDF могут быть большие поля, избыточные отступы или содержимое, неоправданно уменьшенное в масштабе.
Следующие настройки помогают максимально использовать полезную площадь страницы и обеспечить чистое отображение счёта.
sheet.PageSetup.LeftMargin = 0.0
sheet.PageSetup.RightMargin = 0.0
sheet.PageSetup.TopMargin = 0.0
sheet.PageSetup.BottomMargin = 0.0
# Вместить рабочий лист на одну страницу
workbook.ConverterSetting.SheetFitToPage = True
Эти настройки:
Убирают ненужные поля
Максимизируют доступное пространство
Улучшают читаемость
Дают более профессиональный вид PDF
Обработка длинных счетов
Если счёт может содержать много товаров и занимать несколько страниц, размещение всего листа на одной странице обычно нежелательно, потому что содержимое становится слишком мелким.
В этом случае настройте лист на соответствие ширине страницы, разрешив неограниченную высоту.
sheet.PageSetup.FitToPagesWide = 1
sheet.PageSetup.FitToPagesTall = 0
Это указывает Excel:
Уместить счёт в пределах ширины одной страницы
Автоматически создавать дополнительные страницы по вертикали при необходимости
В результате длинные счета остаются читаемыми, сохраняя исходное форматирование.
Экспорт счёта в PDF
Наконец, сохраните книгу как PDF-документ.
workbook.SaveToFile(
"Invoice.pdf",
FileFormat.PDF
)
Поскольку макет листа уже оптимизирован, экспортированный PDF сохраняет предполагаемый внешний вид счёта.
# Для длинного счёта, превышающего одну страницу, установите FitToPagesTall = 0,
# чтобы разрешить размещение на нескольких страницах по вертикали
# sheet.PageSetup.FitToPagesWide = 1
# sheet.PageSetup.FitToPagesTall = 0
workbook.SaveToFile(
"Invoice.pdf",
FileFormat.PDF
)
workbook.Dispose()
Заключение
Использование Excel в качестве движка для шаблонов счетов может значительно упростить генерацию счетов. Храня макеты, формулы и форматирование в электронной таблице, разработчики могут сосредоточиться на предоставлении данных, а не на управлении деталями представления.
Сочетание шаблона Excel с Free Spire.XLS для Python позволяет автоматизировать весь процесс — от заполнения данных счёта до создания профессиональных PDF-документов — с удивительно небольшим количеством кода.
Когда раздел позиций счёта реализован как таблица Excel, решение становится ещё более поддерживаемым. Форматирование, формулы и расширение таблицы обрабатываются автоматически, что упрощает поддержку счетов любого размера, сохраняя вашу кодовую базу чистой и простой.
Представьте дерево. У него есть корень, от него отходят ветки, от больших веток - более мелкие, а затем листья.
Примерно так же устроены иерархические справочники в информационных системах. И как же можно понять: что есть ветка, а что есть лист в этом иерархическом справочнике?
В канале Аналитика FM я часто разбираю такие ситуации - когда задача вроде решаема, но без нормальной структуры превращается в кашу.
Например:
📁 Транспорт ├── Легковой транспорт │ ├── Седаны │ └── Кроссоверы └── Грузовой транспорт ├── Малотоннажный └── Тягачи
Или:
📁 Товары ├── Электроника │ ├── Телефоны │ └── Ноутбуки └── Бытовая техника ├── Холодильники └── Стиральные машины
Структурно это выглядит так: 1 Транспорт ├── 2 Легковой транспорт │ ├── 4 Седаны │ └── 5 Кроссоверы └── 3 Грузовой транспорт └── 6 Тягачи
Каждая запись имеет:
id - собственный идентификатор;
parent_id - идентификатор родительского элемента.
Например:
id = 4 name = Седаны parent_id = 2
Это означает:
Седаны → Легковой транспорт → Транспорт
Именно благодаря полю parent_id база понимает, что "Седаны" и "Кроссоверы" относятся к одной ветке дерева.
Если подниматься по родителям вверх, то рано или поздно мы придём к общему узлу - корню ветки.
Получается, что вся иерархия строится буквально на одном поле: parent_id
Для чего нужны иерархические справочники?
Они позволяют хранить данные не просто списком, а показывать связи между объектами.
Благодаря этому можно:
✅ группировать данные; ✅ строить отчёты по категориям; ✅ наследовать свойства от родительских узлов; ✅ задавать правила сразу для целой ветки; ✅ быстро находить все дочерние элементы.
Например, если правило применяется ко всей категории "Легковой транспорт", то оно автоматически действует и для седанов, и для кроссоверов, и для любых новых подкатегорий, которые появятся позже.
Где используются?
📌 MDM-системы (Master Data Management); 📌 каталоги товаров интернет-магазинов; 📌 банковские и страховые системы; 📌 ERP и CRM; 📌 классификаторы услуг и продуктов; 📌 организационная структура компании; 📌 государственные классификаторы и справочники.
Преимущества
✔ Гибкость. Можно добавлять новые ветки без изменения структуры данных.
✔ Удобная аналитика. Легко получить данные как по конкретному элементу, так и по всей категории.
✔ Наследование правил. Одно правило может применяться сразу к тысячам объектов.
✔ Масштабируемость. Структура может содержать десятки и сотни уровней вложенности.
Недостатки
❌ Сложность запросов. Иногда, чтобы найти всех потомков или родителей, приходится строить рекурсивные запросы.
❌ Производительность. Глубокие иерархии могут существенно замедлять выполнение запросов.
❌ Риск циклических ссылок. Если по ошибке сделать узел потомком самого себя, можно получить бесконечный цикл.
❌ Сложность сопровождения. Изменение структуры верхних уровней может затронуть большое количество дочерних элементов.
Как с ними работать?
При работе с иерархическими справочниками чаще всего приходится решать четыре задачи:
🔹 найти всех потомков узла; 🔹 найти всех родителей элемента; 🔹 определить, принадлежит ли элемент определённой ветке; 🔹 определить, к какому верхнему узлу относится конкретный элемент.
В Oracle для этого используются специальные иерархические запросы:
START WITH ... CONNECT BY ...
Именно они позволяют "обходить дерево" вверх или вниз по веткам.
В канале Аналитика FM (клик :-) ) уже готов пост про конструкцию START WITH и CONNECT BY.
Подписывайся, если интересно разбираться в особенностях работы аналитика.
Иерархический справочник - это не просто список значений. Это способ описать реальные взаимосвязи между объектами и сделать систему более гибкой и управляемой.
А одна маленькая колонка parent_id превращает обычную таблицу в целое дерево данных.
Формулы Microsoft Excel позволяют мгновенно и без ошибок производить сложнейшие расчеты. Их ценность возрастает в разы, когда приходится работать с огромными массивами данных. Стоит запустить правильный алгоритм, и Excel за считанные секунды перелопатит тонны информации, выдав готовый результат.
В этой статье мы разберем пять ключевых типов формул и функций, которые заложат прочный фундамент для вашей аналитической работы. Попутно мы покажем несколько простых способов их ввода в таблицу.
Все примеры мы будем показывать в версии Excel для Windows, входящей в подписку Microsoft 365. Если у вас установлена другая версия программы, интерфейс может слегка отличаться, однако логика и синтаксис остаются неизменными.
Для владельцев определенных подписок M365 доступна генеративная нейросеть – ИИ-помощник Copilot. К теме искусственного интеллекта мы вернемся ближе к концу статьи, а начнем с детального разбора базовых формул и функций.
О чем расскажем:
Что такое формула в Excel?
Что такое функция?
Базовые матоперации и функции
Логическая функция ЕСЛИ
Функции СУММЕСЛИ и СЧЁТЕСЛИ
Функция ССЫЛКА/СЦЕПИТЬ
Функция ВПР
Как создавать формулы с помощью Copilot
❯ Что такое формула в Excel?
По сути, формула – математическое или логическое выражение, производящее операции над содержимым ячеек. С их помощью можно делать расчеты любой сложности: от банального сложения чисел в столбце до расчета эксцесса (показателя островершинности распределения) в сложных статистических выборках. Формулы незаменимы, когда нужно превратить цифры в наглядные бизнес-показатели, на основе которых принимаются важные решения.
❯ Что такое функция?
Функция – своего рода «заготовленная» формула, встроенная в сам Excel. Это готовый шаблон, облегчающий проведение расчетов. Всего в программе насчитывается около 500 встроенных функций, и этот арсенал ежегодно пополняется. К счастью, для решения 90% повседневных задач за глаза хватит всего десятка самых популярных инструментов.
❯ 1. Базовые математические операции и функции
Запомните золотое правило: любая формула в Excel всегда начинается со знака равенства (=).
Сложение, вычитание, умножение и деление
Допустим, нам нужно сложить значения двух ячеек. Кликните по пустой ячейке, где должен появиться результат, и введите знак =, дав программе понять, что далее последует формула.
Начало ввода формулы в Excel
Теперь щелкните по ячейке с первым слагаемым. Ее адрес (например, A2) автоматически отобразится в строке ввода сразу после знака равенства.
При выборе ячейки ее адрес автоматически подставляется в формулу
Введите знак плюс (+), а затем кликните по ячейке со вторым числом – ее адрес (например, A3) встанет на свое место в формуле. В итоге выражение для сложения двух ячеек примет классический вид:
=A2+A3
Готовая формула сложения видна как в самой ячейке, так и в строке формул над листом
Обратите внимание: формула дублируется в строке формул, расположенной прямо над листом таблицы. После ввода знака = вы можете продолжать писать выражение непосредственно там. Зачастую редактировать длинные формулы в этой специальной строке удобнее, чем тесниться внутри самой ячейки.
Если нужно приплюсовать еще несколько чисел, продолжайте ставить знак плюс и кликать по нужным ячейкам. Закончив ввод, нажмите клавишу Enter – и вуаля, на месте формулы появится готовая сумма.
Нажмите Enter, чтобы увидеть итоговый результат вычислений
Вычитание, умножение и деление строятся точно так же. Достаточно сменить математический оператор: вместо плюса поставить дефис (–) для вычитания, звездочку (*) для умножения или косую черту (/) для деления.
Примеры вычитания, умножения и деления. Каждая формула отображается в строке формул, а итоговый результат – в целевой ячейке
Быстрое сложение с помощью функции СУММ (SUM)
Если чисел много, складывать их поодиночке утомительно. Куда практичнее использовать встроенную функцию СУММ (в англоязычной версии – SUM).
В русской версии Excel разделителем аргументов функций служит точка с запятой (;), а в английской – запятая (,). Это важно учитывать, чтобы избежать ошибок при вводе.
Для начала выберите ячейку для вывода результата. Наберите =СУММ (или =SUM). Excel тут же предложит подходящие функции из выпадающего списка. Дважды щелкните по строке СУММ, чтобы открыть круглую скобку.
Запуск функции СУММ
Под ячейкой появится всплывающая подсказка с синтаксисом:
=СУММ(число1; [число2]; …)
Чтобы сложить разрозненные ячейки, просто кликайте по ним, разделяя адреса точкой с запятой. Также адреса можно ввести вручную с клавиатуры.
Если нужно суммировать целый диапазон (непрерывный столбец или строку), выберите первую ячейку группы, зажмите клавишу Shift и кликните по последней. Еще вы можете вписать этот диапазон вручную через двоеточие – выражение A2:A7 охватит ячейки A2, A7 и всё, что между ними.
Выделение диапазона ячеек для суммирования
Убедившись, что все нужные ячейки выделены, нажмите Enter. Готово! На экране появится итоговая сумма. Если снова щелкнуть по этой ячейке, в строке формул отразится полное выражение. В нашем примере это:
=СУММ(A2:A7)
В строке формул видна функция СУММ, в самой ячейке – готовый результат
Важное свойство формул в Excel – динамичность (или относительность). Если изменить значение в любой из ячеек исходного диапазона, итоговая сумма автоматически пересчитается.
При изменении слагаемых результат пересчитывается автоматически
Если же вам нужно «зафиксировать» полученное число, превратив его в статичное значение (константу, которая не изменится при редактировании исходных строк), скопируйте ячейку (клик правой кнопкой мыши → «Копировать»). Затем снова щелкните по ней правой кнопкой мыши и в разделе «Параметры вставки» выберите пункт «Значения» (иконка с планшетом и цифрами 123).
Вставка в режиме «Значения» разрывает связь с исходными ячейками, фиксируя полученный результат
Теперь при выборе этой ячейки в строке формул будет отображаться обычное статичное число, а не алгоритм расчета.
Теперь в ячейке находится обычное статичное значение
Лайфхак: в некоторых случаях процесс можно ускорить благодаря функции «Автосумма». Если у вас есть непрерывный ряд чисел в строке или столбце, поставьте курсор в соседнюю пустую ячейку (справа от строки или снизу под столбцом) и нажмите кнопку Автосумма (со знаком греческой буквы сигма Σ), которая находится на вкладке «Главная» в правой части ленты. Программа сама определит диапазон данных, и вам останется лишь подтвердить расчет нажатием Enter.
Автосумма – самый быстрый способ сложить числа в строке или столбце
Расчет среднего значения с помощью функции СРЗНАЧ (AVERAGE)
Чтобы вычислить среднее арифметическое, проделайте те же шаги, но введите функцию =СРЗНАЧ (=AVERAGE), затем выделите нужные ячейки с числами.
Быстро рассчитать среднее арифметическое поможет СРЗНАЧ
Лайфхак: для вычисления среднего значения тоже есть быстрый ярлык. Поставьте курсор справа от заполненной строки или под столбцом с числами. Нажмите на стрелку рядом с «Автосуммой» и выберите в выпадающем меню пункт Среднее, после чего подтвердите выбор клавишей Enter. Excel рассчитает среднее арифметическое.
Функция расчета среднего значения также вынесена в меню кнопки «Автосумма»
❯ 2. Логическая функция ЕСЛИ (IF)
Этот инструмент позволяет автоматизировать принятие решений прямо внутри таблицы по принципу «если условие выполняется, то переходим к действию А, иначе – к действию Б». Программа проверяет заданное логическое условие и выводит результат в зависимости от исхода проверки. К примеру, вы можете настроить проверку возраста: если в ячейке указано число 18 или больше, вернуть «Да», если меньше – «Нет».
Для разнообразия опробуем еще один способ ввода формул – через вкладку «Формулы». Там все встроенные функции разложены по полочкам: автосумма, финансовые, логические, текстовые, даты и времени и т. д. Классификация очень выручит, когда не помнишь точное название инструмента или сомневаешься в правильности написания.
Чтобы воспользоваться нужной командой, выделите пустую ячейку, откройте вкладку Формулы, нажмите Логические и выберите в списке пункт ЕСЛИ (IF).
Другой вариант – нажать Вставить функцию (fx), расположенную в левом углу той же панели. В открывшемся окне отобразится список часто используемых инструментов.
Если не хотите писать формулу вручную, перейдите на вкладку «Формулы» и воспользуйтесь мастером «Вставить функцию»
Выберите в списке пункт ЕСЛИ и нажмите OK. Если искомой функции нет в перечне часто используемых, смените категорию на «Полный алфавитный перечень» – там вы гарантированно найдете всё, что скрыто в недрах программы.
На экране откроется окно «Аргументы функции», а в выбранной ячейке появится заготовка =ЕСЛИ().
Интерактивное окно «Аргументы функции» помогает безошибочно заполнить все параметры формулы
В окне аргументов увидите три поля с идентичными названиями. В нашем примере с совершеннолетием логический тест проверит, больше или равно ли число в ячейке B2 числу 18. Если да, программа выдаст «Да», если нет – «Нет». Прямо в поля формы впишите соответствующие значения:
Логическое_выражение: B2>=18
Значение_если_истина: "Да"
Значение_если_ложь: "Нет"
Можно обойтись без графической формы и просто прописать формулу в ячейке:
=ЕСЛИ(B2>=18; "Да"; "Нет")
Функция ЕСЛИ в действии
Лайфхак: вам не придется заново прописывать формулу для каждой строки вручную. Просто зажмите левой кнопкой мыши маркер автозаполнения (черный квадрат в правом нижнем углу ячейки) и потяните его вниз по столбцу. Excel автоматически скопирует алгоритм во все строки пониже, подстроив ссылки на ячейки под нужные диапазоны. То есть формула, ссылавшаяся на B2, при перетаскивании ниже станет автоматически считывать данные из B3.
Распространение формулы на остальные строки таблицы с помощью автозаполнения
❯ 3. Выборка по условию: функции СУММЕСЛИ (SUMIF) и СЧЁТЕСЛИ (COUNTIF)
СУММЕСЛИ – продвинутая версия функции сложения. Она позволяет суммировать не всё подряд, а лишь те значения из диапазона, которые соответствуют критерию. Для настройки вам нужно указать целевой диапазон ячеек для проверки, собственно критерий отбора и, опционально, диапазон суммирования (если складывать нужно значения из других ячеек, а не из тех, которые проверяли).
Важное правило: любое текстовое условие, а также любые математические или логические символы здесь заключаются в двойные кавычки.
Представим реальную ситуацию: перед вами таблица продаж и нужно быстро сложить сумму сделок, превышающих 100 долларов. Область проверки – диапазон от C2 до C9, а условие – «>100». Поскольку мы складываем числа из того же самого проверяемого столбца, указывать отдельный диапазон суммирования не нужно. Формула получится простой:
=СУММЕСЛИ(C2:C9; ">100")
Пример вычислений с использованием функции СУММЕСЛИ
А если задача сложнее? Допустим, нам необходима сумма заказов только по региону «East» (восточный). В данном случае диапазон проверки (столбец с регионами B2:B9) и диапазон суммирования (столбец с продажами C2:C9) различаются. Поэтому в аргументах пишем обе эти области:
=СУММЕСЛИ(B2:B9; B2; C2:C9)
Удобно то, что необязательно вбивать само слово "East". Достаточно сослаться на ячейку B2, и программа считает и применит содержащийся в ней текст.
Расчет с разделением диапазона критериев и диапазона сложения в СУММЕСЛИ
По схожему принципу работает СЧЁТЕСЛИ (COUNTIF). Она подсчитывает количество ячеек, которые удовлетворяют заданному критерию. Ее синтаксис лаконичен:
=СЧЁТЕСЛИ(диапазон; условие)
Если хотим подсчитать количество сделок, совершенных в западном регионе («West»), мы задаем область поиска (B2:B9) и критерий (значение в ячейке B3). В итоге получится выражение:
=СЧЁТЕСЛИ(B2:B9; B3)
Функция СЧЁТЕСЛИ подсчитывает количество ячеек, соответствующих вашим критериям
Если перед вами стоит еще более хитрая задача – например, подсчитать общие продажи книг именно в восточном регионе или узнать количество чеков выше 100 долларов исключительно на западе, – Excel предложит тяжелую артиллерию: функции СУММЕСЛИМН (SUMIFS) и СЧЁТЕСЛИМН (COUNTIFS).
4. Склеивание строк: функция СЦЕПИТЬ (CONCAT)
Иногда данные хранятся по кусочкам в разных ячейках, но их нужно объединить в одну строку. Популярный пример – соединить разнесенные по разным столбцам имя и фамилию. Инструмент также отлично решает задачи по сборке почтовых адресов, склейке номеров артикулов, генерации путей к файлам на диске или формированию URL-ссылок. Базовая структура выглядит так:
=СЦЕПИТЬ(текст1; текст2; текст3; …)
Попробуем собрать воедино имя и фамилию из соседних ячеек, не забыв добавить разделительный пробел между ними. Поставим курсор в пустую ячейку C2, введем обрывочное =СЦЕ и кликнем на предложенную функцию СЦЕПИТЬ (CONCAT). Сначала выберем ячейку с именем (A2), затем поставим разделитель, введем пустой пробел в кавычках (" "), снова поставим разделитель и укажем ячейку с фамилией (B2). Завершаем ввод клавишей Enter. Выражение примет следующий вид:
=СЦЕПИТЬ(A2; " "; B2)
Осталось потянуть маркер автозаполнения вниз от ячейки C2, чтобы моментально склеить имена для всех остальных сотрудников в таблице.
Функция СЦЕПИТЬ бесшовно соединила значения из столбцов А и B в словосочетания
❯ 5. Легендарная функция ВПР (VLOOKUP)
Это безоговорочный фаворит аналитиков и одна из самых популярных функций в Excel. ВПР (VLOOKUP) расшифровывается как «вертикальный просмотр». Она ищет заданное значение в крайнем левом столбце диапазона и выводит данные из соседней ячейки в той же строке. Полезно, когда вы хотите сопоставить две разные таблицы, вытащить недостающие реквизиты или сверить два независимых списка.
Чтобы запустить механизм, понадобятся три обязательных аргумента и один опциональный:
Искомое_значение (строка или число, которое пытаемся найти);
Таблица (целевой диапазон ячеек справочника, где осуществляется поиск);
Номер_столбца (порядковый номер колонки в таблице справочника, откуда нужно забрать искомое значение);
Интервальный_просмотр (необязательный аргумент выбора точности поиска: ИСТИНА или ЛОЖЬ). Вариант «ЛОЖЬ» означает, что нужен только стопроцентно точный результат. Выбор «ИСТИНА» разрешает искать близкое приближенное значение. Если опустить параметр, Excel применит поиск по умолчанию – «ИСТИНА».
Держите в уме критически важное условие: искомое значение обязано находиться исключительно в самом первом (крайнем левом) столбце выделяемой таблицы справочника. Этот столбец считается первым (индекс 1), следующий за ним вправо – вторым (индекс 2) и так далее.
Рассмотрим пример: нужно узнать, к какому региону прикреплен сотрудник.
Сначала укажем объект поиска – имя «Mike» (ячейка A2).
Далее выделим диапазон ячеек со справочником (наша целевая таблица поиска) – F2:G8.
Затем укажем, из какого столбца забирать результат. Считаем колонки слева направо в границах нашего справочника F2:G8. Название региона находится во второй колонке, значит, пишем цифру 2.
И наконец, определимся с точностью совпадения. Режим «ИСТИНА» обычно используют для числовых интервалов. Нам же необходимо железно найти конкретного человека, поэтому выбираем ЛОЖЬ (чтобы гарантировать точное совпадение). Большинство опытных пользователей по умолчанию ставят в конце формулы именно «ЛОЖЬ» (или 0).
Итоговый вид формулы:
=ВПР(A2; F2:G8; 2; ЛОЖЬ)
Функция ВПР – идеальный навигатор для стыковки связанных данных в крупных информационных массивах
Конечно, на крохотной демонстрационной таблице преимущество неочевидно. Но когда перед вами база на десятки тысяч позиций, ВПР сэкономит часы монотонного труда и застрахует от опечаток.
❯ Пишем формулы без усилий: ИИ-помощник Copilot
Если вы счастливый обладатель доступа к Copilot в Excel, можно делегировать составление формул искусственному интеллекту. Вам нужно лишь описать задачу человеческим языком, а нейросеть спроектирует нужную синтаксическую конструкцию. Сейчас разберем два наглядных примера.
Чтобы активировать ИИ, нажмите на значок Copilot на панели инструментов или в правом нижнем углу экрана.
Чтобы призвать Copilot, кликните по кнопке. Она может располагаться на ленте или парить в правом нижнем углу рабочего окна Excel
Справа откроется боковая панель ИИ-чата. Сюда мы и будем отправлять наши пожелания. Попробуем с простого: попросим ИИ просуммировать наши сделки и четко укажем ячейку для расположения формулы:
Создай в ячейке B7 формулу для подсчета суммы всех продаж
Сформулируйте задачу для Copilot простым языком
Нажмите кнопку отправки запроса. Помощник проанализирует контекст страницы, соберет нужную формулу и добавит ее в нужную ячейку.
Нейросеть сгенерировала корректный математический код, и в ячейке B7 появился верный ответ
Позовем ИИ на помощь в более запутанном кейсе, представленном на скриншоте ниже.
Разнородный массив данных для следующего теста
Отправим нейросети многоступенчатый запрос:
Создай в ячейке G2 формулу, рассчитывающую общую выручку за единицу товара с учетом скидки на каждую покупку. Затем протяни эту формулу вниз для всех остальных строк и сформируй сводную таблицу с суммой выручки по регионам (Region).
Отправляем промпт на обработку и наблюдаем за магией. Результат работы ИИ выглядит круто:
ИИ за секунду вывел все расчеты, достроил столбец и собрал сводную аналитику по регионам
Лайфхак: работать с Copilot станет комфортнее, если вы предварительно отформатируете рабочий диапазон как умную таблицу. Так нейросеть будет четко видеть границы структурированных данных и задействует нужные диапазоны без промахов.
Для дальнейшего изучения
Сегодня вы заглянули за занавес возможностей Excel, и это лишь вершина гигантского ледника. Как только освоитесь с базовыми алгоритмами, откроются сотни готовых инструментов программы и огромный набор вложенных функций разного уровня сложности. Идеальная отправная точка для углубленного изучения – справочник Microsoft «Общие сведения о формулах в Excel».
всем привет как оставить сумму в формуле Microsoft Excel без слагаемого? например 2+2=4 если убрать одну из двоек то пропадёт и 4 а мне надо чтобы осталась 4 в столбце
А я с PowerBI могу помочь. Дизайн, оптимизация моделей, dax. Насколько позволит формат комментов, конечно.
Вообще если есть потребность, сигнальте - были мысли начать просветительской деятельностью в этом направлении заниматься (массово и бесплатно), если будет спрос, начну писать/снимать.
