0 просмотренных постов скрыто
Ответ на пост «Джентльменский набор функций Excel, без которых вас (скорее всего) не возьмут в приличный офис»2
Млин... 2026 год 4 года ссанкций и прочего непотребства.
И вы продолжаете учить людей монопольному продукту от мелкомягких? Дайте угадаю: вы еще и на айфоне сидите?
Тут только два варианта может быть: или мазохизм или кретинизм.
Libreoffice calc решает практически все требуемые задачи, бесплатен, менее глючен.
Но нет, мы простых путей не ищем.
Да какой, часто мне лень запускать scilab, я обработку экспериментальных данных небольшой сложности делаю в либре. Более чем хватает его мат аппарата.
Джентльменский набор функций Excel, без которых вас (скорее всего) не возьмут в приличный офис2
Всем привет. Что-то довольно сложная и интересная тема про Power Query не зашла :) Так что сегодня попробуем поговорить про что-то простое :)
Каждый раз, когда в требованиях к вакансии пишут «уверенный пользователь ПК и Excel», HR-специалисты втайне надеются, что вы умеете не только красить ячейки в желтый цвет и нажимать кнопку автосуммы. В реальности существует вполне конкретный базовый набор формул. Если вы их знаете — значит, ваши шансы явно выше, чем у тех, кто с ними не знаком. В своем видео я разобрал те самые функции, которые превращают новичка в крепкого офисного бойца. Базовую математику вроде СУММ, МИН, МАКС и СРЗНАЧ опустим (это база по умолчанию) и перейдем сразу к тяжелой артиллерии.
1. Король логики: ЕСЛИ (и его прокачанный брат ЕСЛИМН)
Функция ЕСЛИ (=ЕСЛИ(условие; значение_если_истина; значение_если_ложь)) — основа любых расчетов по условиям. Например, посчитать премию: если менеджер продал больше чем на 2 млн — ему 15%, если меньше — 10%.
Проблема: Что делать, если условий много? (До 5 лет стажа — одна премия, от 5 до 10 — другая, выше 10 — третья). Раньше приходилось строить «матрешки» из вложенных функций: ЕСЛИ(ЕСЛИ(ЕСЛИ...)), в которых легко сломать голову и потерять закрывающую скобку.
Решение для Excel 2019 и выше: Функция ЕСЛИМН. Она избавляет от вложенности. Вы просто прописываете последовательные пары: Условие1; Результат1; Условие2; Результат2 и так далее. Намного нагляднее и проще для восприятия.
2. Санитар таблиц: ЕСЛИОШИБКА
Знакома ситуация, когда вы построили красивый отчет, но в паре ячеек вылезло уродливое #Н/Д, #ДЕЛ/0! или #ЗНАЧ!? Это мгновенно портит вид документа, а у руководства возникают лишние вопросы.
Функция =ЕСЛИОШИБКА(выражение; значение_если_ошибка) перехватывает любой системный сбой. Вместо пугающих символов вы можете заставить Excel выводить аккуратный текст (например, "Нет номера", "Проверить данные") или просто оставлять ячейку пустой (писать ""). Оборачивайте в неё свои сложные расчеты, и отчеты всегда будут выглядеть профессионально. Но помните, что ЕСЛИОШИБКА всего лишь маскирует ошибки, а не исправляет их!
3. Умный подсчет: СУММЕСЛИМН (и компания)
Обычная сумма считает всё подряд. Но в работе чаще нужно сложить только продажи конкретного товара («Яблоки») или продажи определенного менеджера за конкретный месяц.
Многие по старинке используют СУММЕСЛИ (для одного условия), но лучше сразу приучить себя к =СУММЕСЛИМН(...). Она универсальна: работает как с одним, так и с десятком условий одновременно. Аналогично работают функции СЧЁТЕСЛИМН (посчитать количество строк по критериям) и СРЗНАЧЕСЛИМН (найти среднее арифметическое по условиям).
4. Укрощение текста: СЦЕПИТЬ, СЖПРОБЕЛЫ и ПРОПНАЧ
Excel — редактор табличный, но текстового хаоса в нем обычно не меньше. Самый частый кошмар: при загрузке данных из базы ФИО сотрудников разлетаются по разным столбцам, пишутся маленькими буквами или обрастают кучей невидимых лишних пробелов.
Эту проблему решает комбо из трех функций:
СЦЕПИТЬ (или СЦЕП в новых версиях) — собирает текст из разных ячеек в одну строку (не забываем вставлять пробелы " " между фамилией и именем).
СЖПРОБЕЛЫ — автоматически удаляет все лишние пробелы в начале, конце и между словами, оставляя ровно по одному.
ПРОПНАЧ — делает первую букву каждого слова заглавной, а остальные строчными.
Если обернуть их друг в друга: =ПРОПНАЧ(СЖПРОБЕЛЫ(СЦЕПИТЬ(...))), то даже самый «кривой» текст превратится в идеальные, аккуратные ФИО. (Бонус: если нужно сделать ВСЕ буквы заглавными, используйте ПРОПИСН, а если маленькими — СТРОЧН).
5. Магия дат: СЕГОДНЯ, РАБДЕНЬ, ЧИСТРАБДНИ и секретная РАЗНДАТ
Работа с дедлайнами и сроками — рутина любого офиса.
=СЕГОДНЯ() — У неё нет аргументов. Вы один раз вставляете её в ячейку, и каждый раз при открытии файла Excel будет подставлять актуальную текущую дату.
=РАБДЕНЬ(начальная_дата; количество_дней; [праздники]) — незаменима, если нужно рассчитать дату выполнения задачи с учетом выходных и праздничных дней (главное — заранее выписать список государственных праздников в отдельный диапазон и зафиксировать его через F4).
=ЧИСТРАБДНИ(...) — считает точное количество рабочих дней между двумя датами.
Секретная функция РАЗНДАТ: Если вы введете её в Excel, программа не выдаст привычную подсказку по аргументам — функция официально считается «скрытой». Тем не менее, она идеально считает точный возраст сотрудника или стаж в годах, месяцах или днях на текущую дату в связке с функцией СЕГОДНЯ.
6. Священный Грааль: ВПР и ПРОСМОТРX
Если вы на собеседовании скажете, что знаете ВПР (VLOOKUP), уровень доверия к вам вырастет на 37%. Эта функция позволяет сопоставить две таблицы по ключевому полю (например, подтянуть оклад сотрудника из общего справочника по его табельному номеру).
Но если у вас Office 2021+: Забудьте про ВПР как про страшный сон (нет) и используйте =ПРОСМОТРX (XLOOKUP). Она гораздо современнее, безопаснее, не требует указывать номер столбца и искать точное совпадение через 0/ЛОЖЬ — всё работает «из коробки» и интуитивно понятно.
7. Выход из безвыходных ситуаций: ИНДЕКС + ПОИСКПОЗ
У классической ВПР есть один критический недостаток — она умеет искать данные только слева направо. То есть ключевой столбец (например, ID или код товара) обязательно должен быть крайним левым в исходной таблице. Если то, что вы ищете, находится левее — ВПР не подойдёт.
Что делать? Физически переносить столбцы в таблице? Не нужно. Связка функций =ИНДЕКС(массив; ПОИСКПОЗ(что_ищем; где_ищем; 0)) полностью заменяет ВПР, но при этом ей абсолютно всё равно, в каком порядке расположены столбцы. Более того, эта связка умеет делать двумерный поиск (одновременно и по строкам, и по столбцам).
Итог:
Освоив эти функции, вы закроете огромную часть повседневных аналитических задач в офисе.
А каков ваш личный топ функций Excel, без которых вы не представляете рабочий день? Пишите в комментариях, соберем альтернативный список!
Ссылка на файл, кому надо.
Показать полностью
Добавление или чтение формул Excel с помощью C#: Полное руководство
В повседневных задачах обработки офисных данных формулы и функции Excel являются основой автоматизированных вычислений и анализа данных. Для .NET разработчиков распространённой и практичной задачей является программное добавление формул в файлы Excel или извлечение логики формул из существующих таблиц. В этой статье мы используем легковесную и бесплатную библиотеку Free Spire.XLS for .NET, чтобы подробно продемонстрировать, как добавлять и читать формулы Excel с помощью C#.
Подготовка: подключение Free Spire.XLS
Перед началом кодирования нам нужно добавить Free Spire.XLS for .NET в проект. Этот компонент легко устанавливается через менеджер пакетов NuGet. Выполните следующую команду в консоли диспетчера пакетов:
Install-Package FreeSpire.XLS
После установки мы можем использовать пространство имён using Spire.Xls; для доступа ко всем классам и методам, связанным с операциями Excel.
1. Добавление формул в Excel
Сценарии добавления формул очень разнообразны: например, массовый расчёт общего объёма продаж, вычисление среднего балла студентов, создание динамических отчётов и т.д. Пример ниже показывает, как создать новую книгу Excel, записать в неё базовые данные и добавить формулы в определённые ячейки.
using Spire.Xls;
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
// Добавление базовых числовых данных
sheet.Range[1, 1].NumberValue = 1;
sheet.Range[1, 2].NumberValue = 2;
sheet.Range[1, 3].NumberValue = 3;
sheet.Range[1, 4].NumberValue = 4;
sheet.Range[1, 5].NumberValue = 5;
sheet.Range[1, 6].NumberValue = 6;
// Добавление функции: среднее значение (AVERAGE)
string averageFormula = "=AVERAGE(Sheet1!$A$1:A$6)";
sheet.Range[2, 1].Formula = averageFormula;
// Добавление арифметической формулы
string calcFormula = "=1+2+3+4+5-6-7+8-9";
sheet.Range[3, 1].Formula = calcFormula;
workbook.SaveToFile("AddFormulasAndFunctions.xlsx", ExcelVersion.Version2016);
workbook.Dispose();
Пояснение ключевых моментов кода:
Свойство sheet.Range[строка, столбец].Formula напрямую присваивает строку формулы ячейке. Синтаксис полностью совпадает с родными формулами Excel.
В формулах поддерживаются абсолютные ссылки (например, $D$2) и относительные ссылки (например, F$2), как и в Excel.
Можно добавлять не только простые арифметические операции, но и встроенные функции, такие как AVERAGE, SUM, IF.
2. Чтение существующих формул из Excel
В реальной работе часто требуется проанализировать отчёты Excel, созданные другими, и извлечь логику вычислений для аудита, переноса или документирования. Следующий код демонстрирует загрузку существующего файла Excel, перебор всех используемых ячеек и поиск тех, которые содержат формулы.
using Spire.Xls;
using System.IO;
using System.Text;
Workbook workbook = new Workbook();
workbook.LoadFromFile("Formulas.xlsx");
Worksheet sheet = workbook.Worksheets[0];
StringBuilder sb = new StringBuilder();
CellRange usedRange = sheet.AllocatedRange;
foreach (CellRange cell in usedRange)
{
if (cell.HasFormula)
{
string cellName = cell.RangeAddressLocal;
string formula = cell.Formula;
sb.AppendLine($"{cellName} содержит формулу: {formula}");
}
}
File.WriteAllText("ReadFormulasAndFunctions.txt", sb.ToString());
Пояснение ключевых моментов кода:
Свойство cell.HasFormula возвращает булево значение для быстрой проверки, содержит ли ячейка формулу.
cell.Formula возвращает строковое представление формулы, например =AVERAGE(D2:F2).
AllocatedRange позволяет получить диапазон использованных данных на листе, избегая перебора всех пустых ячеек и повышая эффективность.
Извлечённая информация о формулах записывается в текстовый файл для последующего анализа или архивации.
3. Практические сценарии использования
Освоив добавление и чтение формул, можно создать множество полезных инструментов автоматизации:
Генератор отчётов : автоматическая запись исходных данных в шаблон Excel и динамическое добавление статистических формул, таких как SUM, COUNTIF.
Инструмент аудита формул : массовое чтение сложных формул в финансовых отчётах и проверка их логики для предотвращения ошибок.
Помощник миграции формул : извлечение формул из старых файлов Excel и массовое применение их в соответствующих местах новых шаблонов.
Учебная вспомогательная система : автоматическая проверка заданий Excel, сданных студентами, чтение формул для определения правильности шагов решения.
4. Примечания и рекомендации
Строка формулы должна начинаться со знака равенства =, иначе компонент будет обрабатывать её как обычный текст.
Диапазон ячеек, на который ссылается формула, должен быть корректным. Ссылка на несуществующие данные может вызвать ошибку #REF! при открытии файла в Excel.
Free Spire.XLS — бесплатная версия, но имеет ограничение по количеству страниц (не более 5 страниц на рабочий лист). Для большинства небольших и средних проектов этого вполне достаточно.
Для работы с очень большими файлами или снятия ограничения на количество страниц можно рассмотреть переход на коммерческую версию Spire.XLS.
Заключение
Из примеров кода в этой статье мы ясно видим, что использование Free Spire.XLS for .NET для работы с формулами Excel в C# является интуитивно понятным и эффективным. Будь то добавление сложной логики вычислений в таблицы или обратный анализ существующих формул, этот компонент предоставляет полноценный и простой в использовании API. Надеемся, что эта статья поможет вам более уверенно решать задачи разработки, связанные с формулами Excel, в ваших реальных проектах.
Показать полностью
1
Вопрос по Microsoft Excel
Здравствуйте. Нужна помощь с одним моментом при работе в Microsoft Excel.
Тут люди разные сидят, мож кто в теме..
Есть проблема. К примеру есть информация полученная из разных источников в формате ПДФ
Допустим, пусть там будет перечень запчастей от автомобиля. Перевели мы эти файлы в Excel для удобства работы. Итого 4 файла в Excel с перечнем запчастей.
А можно ли в эти 4 файла в Excel конвертировать или собрать в один?
Что бы получить вместо 4 файлов в эксель 1 в котором указана вся сводная информация по запчастям?
Если да, то как это сделать? Заранее спасибо. Всем респект и уважуха.
Превращаем хаос в порядок: как использовать Unpivot в Power Query для анализа данных
Всем привет. Многие пользователи Excel сталкиваются с проблемой: таблицы, удобные для печати, совершенно непригодны для анализа данных. В этом гайде вы узнаете, как с помощью Power Query превратить «неправильную» таблицу с вложенными заголовками в плоскую структуру, готовую для сводных таблиц и любого анализа .
Если кажется, что букв много - то вот
Проблема: почему обычная таблица не подходит для анализа
Рассмотрим типичный пример таблицы с плановыми и фактическими показателями по продуктам и регионам (здесь подставьте свои данные). Она наглядна для печати и просмотра, но имеет серьёзные недостатки:
Вложенные заголовки: невозможно сравнить показатели по продукту/региону.
Данные на пересечении строк и столбцов: сложно строить сводные таблицы.
Несколько строк заголовков: еxcel не распознаёт структуру правильно.
Решение: нужна «плоская таблица» — с одной строкой заголовков и одним типом данных в каждом столбце .
Шаг 1: Подготовка данных в Excel
1.1 Создание именованного диапазона
Вместо умной таблицы используем именованный диапазон (многие пользователи не любят умные таблицы, да тут нам они особо и не нужны):
Выделите всю таблицу
В имени ячейки (слева сверху) введите понятное имя, например Данные
Обязательно нажмите Enter после ввода имени
1.2 Загрузка в Power Query
Для Excel 2019+:
Вкладка Данные - группа Получить и преобразовать данные - Из таблицы/диапазона
Для Excel 2016:
Вкладка Данные - группа Загрузить & преобразовать - Создать запрос
Для Excel 2010/2013: нужно отдельно скачать надстройку Power Query. Но с официального сайта Microsoft уже не получится это сделать.
Шаг 2: Работа с заголовками (первый этап)
Заголовки в нашей таблице занимают три строки:
2.1 Оставляем только строки заголовков
В редакторе Power Query справа на панели Примененные шаги удалите шаги: «Изменённый тип» и «Повышенные заголовки»
Вкладка Главная - группа Сократить строки - Сохранить строки - Сохранить верхние строки
Укажите 3 (количество строк заголовков)
2.2 Транспонирование таблицы
Цель: превратить горизонтальный массив в вертикальный.
Вкладка Преобразование - Транспонировать
2.3 Заполнение пустых значений
Выделите столбцы с типом данных null (через Ctrl)
Вкладка Преобразование - Заполнить - Заполнить вниз
2.4 Объединение столбцов в один
Выделите три столбца (зажмите Shift)
Вкладка Добавление столбца - Объединить столбцы
Выберите Пользовательский разделитель
Важно: используйте разделитель, которого нет в данных (например, -- двойной дефис)
Нажмите ОК, затем удалите другие столбцы (правая кнопка - Удалить другие столбцы)
2.5 Обратное транспонирование
Вкладка Преобразование - Транспонировать
Теперь столбец превратился в строку — работа с заголовками завершена
Шаг 3: Работа с данными (второй этап)
3.1 Возврат к исходным данным
Важный приём Power Query — работа с шагами:
Перейдите к последнему шагу в истории
В строке формул нажмите f(x) - Добавить шаг
Замените ссылку на Источник (название шага с полными данными). У вас это может быть другое название. Важно, чтобы вы выбрали именно тот шаг, на котором была полная таблица.
Соблюдайте регистр! Power Query чувствителен к заглавным буквам
3.2 Удаление строк заголовков
Вкладка Главная - Удалить строки - Удалить верхние строки
Удалите 3 строки (заголовки)
Отфильтруйте строку «Итого»: правая кнопка - Текстовые фильтры - Не содержит - Итого
3.3 Разделение столбца «Регион + Продукт»
Создаём два столбца из одного:
Выделите столбец Column1
Вкладка Добавление столбца - Условный столбец
Логика: Если Column1 не содержит Продукт то Column1
Нажмите ОК
Теперь заполните столбец вниз:
Выделяем столбец. Вкладка Преобразование - Заполнить - Заполнить вниз
3.4 Удаление строк «Итого» через чередование
Если структура фиксирована (регион + 4 продукта или что-то в этом роде):
Вкладка Главная - Удалить строки - Удалить чередующиеся строки
Первая строка для удаления: 1
Количество строк для сохранения: 4
Power Query будет: удалять 1 строку, сохранять 4, удалять 1, сохранять 4... и так до конца документа
Если структура таблицы не позволяет так сделать, то можно пойти через банальную фильтрацию данных и убрать те строки, в которых содержатся промежуточные итоги.
Шаг 4: Объединение этапов и финальная обработка
4.1 Конкатенация (объединение) двух таблиц
Объединяем таблицу заголовков и таблицу данных:
Перейдите к последнему шагу
Нажмите f(x) → Добавить шаг
В строке формул: =#"Удалённые чередующиеся строки" & #"Транспонированная таблица"
Используйте амперсанд (&) для конкатенации
4.2 Повышение заголовков
Нажмите кнопку слева: Использовать первую строку в качестве заголовков
Переместите столбец Регион в начало (перетащите)
Переименуйте столбцы: Продукт, Регион
4.3 Магия Unpivot (отменить свёртывание столбцов)
Ключевой шаг всей операции:
Выделите столбцы Регион и Продукт (которые нужно оставить как есть). Это те столбцы, которые разворачивать не нужно.
Правая кнопка мыши по любому выделенному столбцу - Отменить свёртывание других столбцов
Теперь показатели превратились в строки
4.4 Разделение столбца с показателем
Выделите объединённый столбец
Вкладка Преобразование - Разделить столбец По разделителю
Пользовательский разделитель: -- (тот же, что использовали ранее)
Нажмите ОК
4.5 Настройка типов данных
Выделите текстовые столбцы - иконка текста
Последнему столбцу (значения) - тип Десятичное число (или тот формат, который вам нужен)
Проверьте, чтобы все типы соответствовали ожидаемым данным
Шаг 5: Выгрузка обратно в Excel
Файл - Закрыть и загрузить в...
Выберите Таблица - на существующий лист
Укажите ячейку (например, J1)
Нажмите ОК
Повторное использование запроса для новых данных
Если приходит похожая таблица (новая неделя, другие регионы/продукты):
В Excel откройте Данные - Запросы и подключения
В запросе измените шаг Источник: вместо Данные напишите Данные_новые (предварительно присвоив диапазону с новой таблицей такое имя)
Нажмите Enter
Выгрузите данные обратно в Excel
Готово! Все шаги применятся автоматически
Если меняется структура таблицы
Если количество продуктов различается (где-то 3, где-то 4) или ещё какая-то логика таблицы меняется, то нужно в запросе изменить/удалить нужный шаг. Например:
Удалите шаг «Удалённые чередующиеся строки»
Вместо этого используйте фильтр: правая кнопка - Текстовые фильтры - Не содержит - Итого
Power Query запомнит новый шаг
Преимущества подхода: что вы получаете
Плоская таблица: можно строить сводные таблицы
Наглядный анализ: сравнивать план/факт по продукту, региону, показателю в сводной
Автоматизация: один раз настроил — используешь бесконечно
Быстрое обновление: поправил данные в источнике - правой кнопкой мыши по любой ячейке таблицы - Обновить
Заключение
Получилось много букв и картинок, но таков путь. Решение не унифицированное: у вас может быть другое количество строк заголовков, другая структура таблицы и так далее. Но главное, это понять суть того, как мы можем сделать это в Power Query. На самом деле, проделав это пару раз, поняв логику своей таблицы и решения, в дальнейшем вы очень сильно упрощаете и ускоряете работу с такими вот чудесными таблицами. Спасибо всем, кто осилил мою простыню :) Искренне надеюсь, что кому-то было полезно.
Показать полностью
16
Как суммировать данные с ошибками и не сойти с ума
Всем привет. Сегодня разберемся с ситуацией, когда вам нужно посчитать общую сумму, но в столбце предательски горят ошибки #Н/Д, #ССЫЛКА! или #ДЕЛ/0! (или любая другая).
Обычная функция СУММ в таком случае выдает ошибку в ответ. Но мы не привыкли отступать! Ловите 4 способа просуммировать всё, что суммируется, игнорируя любую ошибку.
Как обычно, кому лень смотреть - видео
1. Функция АГРЕГАТ (AGGREGATE)
Это, пожалуй, самый элегантный способ. Функция АГРЕГАТ — это как расширенный швейцарский нож. Она умеет делать всё: считать сумму, среднее, максимум (и многое другое), и при этом игнорировать всё лишнее.
Как прописать: =АГРЕГАТ(9; 6; ваш_диапазон).
Что значат цифры: 9 — это команда «Суммировать», а 6 — та самая магическая настройка «Игнорировать ошибки».
Бонус: Она также умеет игнорировать скрытые строки, если вы решите что-то отфильтровать.
2. Формула массива (для любителей классики).
Если вы хотите использовать старую добрую СУММ, вам придется объединить ее с проверкой на вшивость ошибки.
Конструкция: =СУММ(ЕСЛИОШИБКА(диапазон; 0)).
Важно: Если у вас старая версия Excel (2019 и ниже), не забудьте нажать Ctrl + Shift + Enter после ввода, чтобы формула превратилась в формулу массива. Теперь Excel сначала заменит все ошибки на нули в уме, а потом всё сложит.
3. СУММЕСЛИМН (метод для терпеливых)
Можно сказать Экселю буквально: «Сложи мне всё, что НЕ равно ошибке».
Минус: вам придется прописать каждую ошибку вручную. Выглядит это примерно так: ...; диапазон; "<>#Н/Д"; диапазон; "<>#ССЫЛКА!".
Вердикт: работает, но если типов ошибок много, формула превратится в бесконечную простыню. Подойдет, только если у вас везде один и тот же тип ошибки.
4. Промежуточные итоги + Фильтр (метод «на коленке»)
Если новые или сложные формулы — это не ваше, можно пойти по пути наименьшего сопротивления.
Шаг 1: используйте функцию =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9; диапазон) (SUBTOTAL).
Шаг 2: просто отфильтруйте столбец, сняв галочки со всех ошибок.
Результат: функция пересчитает сумму только по видимым ячейкам. Быстро, просто, но требует ручной фильтрации.
Итоговый совет:
Если ошибки в таблице — это временное явление (например, данные еще не подтянулись), используйте АГРЕГАТ. Это самый быстрый и простой (с моей точки зрения) способ.
На этом всё. Всем спасибо, кто дочитал. Надеюсь, было полезно и кому-то поможет :) Если знаете другие интересные способы решить подобную задачу, делитесь в комментариях.
Показать полностью
3