Как сделать красивый отчет в excel?

Отчеты в MS EXCEL

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

В качестве исходной будем использовать таблицу в формате EXCEL 2007 ( Вставка/ Таблицы/ Таблица ), содержащую информацию о продажах партий продуктов. В строках таблицы приведены данные о поставке партии продукта и его сбыте. Аналогичная таблица использовалась в статье Сводные таблицы.

В таблице имеются столбцы:

  • Товар – наименование партии товара, например, «Апельсины»;
  • Группа – группа товара, например, «Апельсины» входят в группу «Фрукты»;
  • Дата поставки – Дата поставки Товара Поставщиком;
  • Регион продажи – Регион, в котором была реализована партия Товара;
  • Продажи – Стоимость, по которой удалось реализовать партию Товара;
  • Сбыт – срок фактической реализации Товара в Регионе (в днях);
  • Прибыль – отметка о том, была ли получена прибыль от реализованной партии Товара.

Через Диспетчер имен откорректируем имя таблицы на «Исходная_таблица» (см. файл примера ).

С помощью формул создадим 5 несложных отчетов, которые разместим на отдельных листах.

Отчет №1 Суммарные продажи Товаров

Найдем суммарные продажи каждого Товара.
Задача решается достаточно просто с помощью функции СУММЕСЛИ() , однако само построение отчета требует определенных навыков работы с некоторыми средствами EXCEL.

Итак, приступим. Для начала нам необходимо сформировать перечень названий Товаров. Т.к. в столбце Товар исходной таблицы названия повторяются, то нам нужно из него выбрать только уникальные значения. Это можно сделать несколькими способами: формулами (см. статью Отбор уникальных значений), через меню Данные/ Работа с данными/ Удалить дубликаты или с помощью Расширенного фильтра. Если воспользоваться первым способом, то при добавлении новых Товаров в исходную таблицу, новые названия будут включаться в список автоматически. Но, здесь для простоты воспользуемся вторым способом. Для этого:

  • Перейдите на лист с исходной таблицей;
  • Вызовите Расширенный фильтр ( Данные/ Сортировка и фильтр/ Дополнительно );
  • Заполните поля как показано на рисунке ниже: переключатель установите в позицию Скопировать результат в другое место; в поле Исходный диапазон введите $A$4:$A$530; Поставьте флажок Только уникальные записи.

  • Скопируйте полученный список на лист, в котором будет размещен отчет;
  • Отсортируйте перечень товаров ( Данные/ Сортировка и фильтр/ Сортировка от А до Я ).

Должен получиться следующий список.

В ячейке B6 введем нижеследующую формулу, затем скопируем ее Маркером заполнения вниз до конца списка:

Для того, чтобы понять сруктурированные ссылки на поля в таблицах в формате EXCEL 2007 можно почитать Справку EXCEL (клавиша F1) в разделе Основные сведения о листах и таблицах Excel > Использование таблиц Excel.

Также можно легко подсчитать количество партий каждого Товара:

Отчет №2 Продажи Товаров по Регионам

Найдем суммарные продажи каждого Товара в Регионах.
Воспользуемся перечнем Товаров, созданного для Отчета №1. Аналогичным образом получим перечень названий Регионов (в поле Исходный диапазон Расширенного фильтра введите $D$4:$D$530).
Скопируйте полученный вертикальный диапазон в Буфер обмена и транспонируйте его в горизонтальный. Полученный диапазон, содержащий названия Регионов, разместите в заголовке отчета.

В ячейке B8 введем нижеследующую формулу:

=СУММЕСЛИМН(Исходная_Таблица[Продажи];
Исходная_Таблица[Товар];$A8;
Исходная_Таблица[Регион продажи];B$7)

Формула вернет суммарные продажи Товара, название которого размещено в ячейке А8, в Регионе из ячейки В7. Обратите внимание на использование смешанной адресации (ссылки $A8 и B$7), она понадобится при копировании формулы для остальных незаполненных ячеек таблицы.

Скопировать вышеуказанную формулу в ячейки справа с помощью Маркера заполнения не получится (это было сделано для Отчета №1), т.к. в этом случае в ячейке С8 формула будет выглядеть так:

=СУММЕСЛИМН(Исходная_Таблица[Сбыт, дней];
Исходная_Таблица[Группа];$A8;
Исходная_Таблица[Продажи];C$7)

Ссылки, согласно правил относительной адресации, теперь стали указывать на другие столбцы исходной таблицы (на те, что правее), что, естественно, не правильно. Обойти это можно, скопировав формулу из ячейки B8, в Буфер обмена, затем вставить ее в диапазон С8:G8, нажав CTRL+V.
В ячейки ниже формулу можно скопировать Маркером заполнения.

Отчет №3 Фильтрация Товаров по прибыльности

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

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

Суммарные продажи подсчитаем следующей формулой массива:
=СУММПРОИЗВ((Исходная_Таблица[Группа]=A8)*
ЕСЛИ($B$5=»(Все)»;1;(Исходная_Таблица[Прибыль]=$B$5))*
Исходная_Таблица[Продажи])

После ввода формулы не забудьте вместо простого нажатия клавиши ENTER нажать CTRL+SHIFT+ENTER.

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

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

Выбрав в фильтре значение Нет (в ячейке B5), сразу же получим отчет о продажах по Группам Товаров, принесших убытки.

Отчет №4 Статистика сроков сбыта Товаров

Вернемся к исходной таблице. Каждая партия Товара сбывалась определенное количество дней (см. столбец Сбыт в исходной таблице). Необходимо подготовить отчет о количестве партий, которые удалось сбыть за за период от 1 до 10 дней, 11-20 дней; 21-30 и т.д.

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

Количество партий, сбытые за определенный период времени, будем подсчитывать с помощью формулы ЧАСТОТА() , которую нужно ввести как формулу массива:

Для ввода формулы выделите диапазон С6:С12, затем в Строке формул введите вышеуказанную формулу и нажмите CTRL+SHIFT+ENTER.

Этот же результат можно получить с помощью обычной функции СУММПРОИЗВ() :
=СУММПРОИЗВ((Исходная_Таблица[Сбыт, дней]>A6)*
(Исходная_Таблица[Сбыт, дней] =B9)*
(Исходная_Таблица[Дата поставки] Похожие задачи

Excel-лайфхаки для тех, кто занимается отчётностью и обработкой данных

В этом посте Ренат Шагабутдинов, ассистент генерального директора издательства «Манн, Иванов и Фербер», делится классными Excel-лайфхаками. Приведённые советы будут полезны для всех, кто занимается различной отчётностью, обработкой данных и созданием презентаций.

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

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

Простые альтернативы ВПР и ГПР, если искомые значения не в первом столбце таблицы: ПРОСМОТР, ИНДЕКС+ПОИСКПОЗ

Функции ВПР (VLOOKUP) и ГПР (HLOOKUP) работают только в том случае, если искомые значения находятся в первом столбце или строке той таблицы, из которой вы планируете получить данные.

В остальных случаях есть два варианта:

  1. Использовать функцию ПРОСМОТР (LOOKUP).
    У неё следующий синтаксис: ПРОСМОТР (искомое_значение; вектор_просмотра; вектор_результата). Но для её корректной работы нужно, чтобы значения диапазона вектор_просмотра были отсортированы по возрастанию:
  2. Использовать сочетание функций ПОИСКПОЗ (MATCH) и ИНДЕКС (INDEX).
    Функция ПОИСКПОЗ возвращает порядковый номер элемента в массиве (с её помощью вы можете найти, в какой строке таблицы искомый элемент), а функция ИНДЕКС возвращает элемент массива с заданным номером (который мы и узнаем с помощью функции ПОИСКПОЗ).Синтаксис функций:
    • ПОИСКПОЗ (искомое_значение; массив_поиска; тип_сопоставления) — для нашего случая нам нужен тип сопоставления «точное сопоставление», ему соответствует цифра 0.
    • ИНДЕКС (массив; номер_строки; [номер_столбца]). В данном случае номер столбца указывать не нужно, так как массив состоит из одной строки.
Читать еще:  Как сделать формулу в excel приход расход остаток?

Как быстро заполнить пустые ячейки в списке

Задача — заполнить ячейки в столбце со значениями сверху (чтобы тематика стояла в каждой строке таблицы, а не только в первой строке блока книг по тематике):

Выделяем столбец «Тематика», нажимаем на ленте в группе «Главная» кнопку «Найти и выделить» → «Выделить группу ячеек» → «Пустые ячейки» и начинаем ввод формулы (то есть ставим знак равно) и ссылаемся на ячейку сверху, просто нажимая стрелку вверх на клавиатуре. После этого нажимаем Ctrl + Enter. После этого можно сохранить полученные данные как значения, так как формулы больше не нужны:

Как найти ошибки в формуле

Вычисление отдельной части формулы

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

  1. Чтобы вычислить часть формулы прямо в строке формул, выделите эту часть и нажмите F9:

    В данном примере была проблема с функцией ПОИСК (SEARCH) — в ней были перепутаны местами аргументы. Важно помнить, что если вы не отмените вычисление части функции и нажмёте Enter, то вычисленная часть так и останется числом.
  2. Нажмите на кнопку «Вычислить формулу» в группе «Формулы» на ленте:


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

Как определить, от чего зависит или на что ссылается формула

Чтобы определить, от каких ячеек зависит формула, в группе «Формулы» на ленте нажмите на кнопку «Влияющие ячейки»:

Появляются стрелки, указывающие, от чего зависит результат вычислений.

Если отображается символ, выделенный на картинке красным цветом, то формула зависит от ячеек, находящихся на других листах или в других книгах:

Щёлкнув на него, мы увидим, где именно находятся влияющие ячейки или диапазоны:

Рядом с кнопкой «Влияющие ячейки» находится кнопка «Зависимые ячейки», работающая аналогично: она отображает стрелки от активной ячейки с формулой к ячейкам, которые зависят от неё.

Кнопка «Убрать стрелки», расположенная в том же блоке, позволяет убрать стрелки к влияющим ячейкам, стрелки к зависимым ячейкам или же оба типа стрелок сразу:

Как найти сумму (количество, среднее) значений ячеек с нескольких листов

Допустим, у вас есть несколько однотипных листов с данными, которые вы хотите сложить, посчитать или обработать как-то иначе:

Для этого в ячейку, в которой вы хотите видеть результат, введите стандартную формулу, например СУММ (SUM), и укажите в аргументе через двоеточие название первого и последнего листов из списка тех листов, что вам нужно обработать:

Вы получите сумму ячеек с адресом B3 с листов «Данные1», «Данные2», «Данные3»:

Такая адресация работает для листов, расположенных последовательно. Синтаксис следующий: =ФУНКЦИЯ (первый_лист:последний_лист!ссылка на диапазон).

Как автоматически строить шаблонные фразы

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

  • Объединяем текст с помощью знака & (можете заменить его функцией СЦЕПИТЬ (CONCATENATE), но в этом нет особого смысла).
  • Текст всегда записывается в кавычках, ссылки на ячейки с текстом — всегда без.
  • Чтобы получить служебный символ «кавычки», используем функцию СИМВОЛ (CHAR) с аргументом 32.

Пример создания шаблонной фразы с помощью формул:

В данном случае, кроме функции СИМВОЛ (CHAR) (для отображения кавычек) используется функция ЕСЛИ (IF), позволяющая изменять текст в зависимости от того, наблюдается ли положительная динамика продаж, и функция ТЕКСТ (TEXT), позволяющая отобразить число в любом формате. Её синтаксис описан ниже:

ТЕКСТ (значение; формат)

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

Автоматизировать можно и более сложные тексты. В моей практике была автоматизация длинных, но рутинных комментариев к управленческой отчётности в формате «ПОКАЗАТЕЛЬ упал/вырос на XX относительно плана в основном из-за роста/снижения ФАКТОРА1 на XX, роста/снижения ФАКТОРА2 на YY…» с меняющимся списком факторов. Если вы пишете такие комментарии часто и процесс их написания можно алгоритмизировать — стоит один раз озадачиться созданием формулы или макроса, которые избавят вас хотя бы от части работы.

Как сохранить данные в каждой ячейке после объединения

При объединении ячеек сохраняется только одно значение. Excel предупреждает об этом при попытке объединить ячейки:

Соответственно, если у вас была формула, зависящая от каждой ячейки, она перестанет работать после их объединения (ошибка #Н/Д в строках 3–4 примера):

Чтобы объединить ячейки и при этом сохранить данные в каждой из них (возможно, у вас есть формула, как в этом абстрактном примере; возможно, вы хотите объединить ячейки, но сохранить все данные на будущее или скрыть их намеренно), объедините любые ячейки на листе, выделите их, а затем с помощью команды «Формат по образцу» перенесите форматирование на те ячейки, которые вам и нужно объединить:

Как построить сводную из нескольких источников данных

Если вам нужно построить сводную сразу из нескольких источников данных, придётся добавить на ленту или панель быстрого доступа «Мастер сводных таблиц и диаграмм», в котором есть такая опция.

Сделать это можно следующим образом: «Файл» → «Параметры» → «Панель быстрого доступа» → «Все команды» → «Мастер сводных таблиц и диаграмм» → «Добавить»:

После этого на ленте появится соответствующая иконка, нажатие на которую вызывает того самого мастера:

При щелчке на неё появляется диалоговое окно:

В нём вам необходимо выбрать пункт «В нескольких диапазонах консолидации» и нажать «Далее». В следующем пункте можно выбрать «Создать одно поле страницы» или «Создать поля страницы». Если вы хотите самостоятельно придумать имя для каждого из источников данных — выберите второй пункт:

В следующем окне добавьте все диапазоны, на основании которых будет строиться сводная, и задайте им наименования:

После этого в последнем диалоговом окне укажите, где будет размещаться отчёт сводной таблицы — на существующем или новом листе:

Отчёт сводной таблицы готов. В фильтре «Страница 1» вы можете выбрать только один из источников данных, если это необходимо:

Как рассчитать количество вхождений текста A в текст B («МТС тариф СуперМТС» — два вхождения аббревиатуры МТС)

В данном примере в столбце A есть несколько текстовых строк, и наша задача — выяснить, сколько раз в каждой из них встречается искомый текст, расположенный в ячейке E1:

Для решения этой задачи можно воспользоваться сложной формулой, состоящей из следующих функций:

  1. ДЛСТР (LEN) — вычисляет длину текста, единственный аргумент — текст. Пример: ДЛСТР (“машина”) = 6.
  2. ПОДСТАВИТЬ (SUBSTITUTE) — заменяет в текстовой строке определённый текст другим. Синтаксис: ПОДСТАВИТЬ (текст; стар_текст; нов_текст). Пример: ПОДСТАВИТЬ (“автомобиль”;“авто”;“”)= “мобиль”.
  3. ПРОПИСН (UPPER) — заменяет все символы в строке на прописные. Единственный аргумент — текст. Пример: ПРОПИСН (“машина”) = “МАШИНА”. Эта функция понадобится нам, чтобы делать поиск без учёта регистра. Ведь ПРОПИСН(“машина”)=ПРОПИСН(“Машина”)

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

ДЛСТР(“Тариф МТС Супер МТС”) – ДЛСТР(“Тариф Супер”) = 6

А затем разделить эту разницу на длину той строки, которую мы искали:

6 / ДЛСТР (“МТС”) = 2

Именно два раза строка «МТС» входит в исходную.

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

В нашем примере формула выглядит следующим образом:

Создание красивого отчёта

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

Вы создали небольшую сводную таблицу (Вставка – Сводная таблица):

Но вашему начальнику не нравится внешний вид отчёта, и он хочет видеть что-то похожее на это:

То есть мы имеем несколько ощутимых трудностей:

1. Исходный внешний вид сводной таблицы не подходит — дизайн отчёта должен соответствовать корпоративным стандартам (цвета, логотипы, спарклайны, стрелки и т.д.). «Дорабатывать напильником» дизайн сводной — долгий и мучительный процесс. И не факт, что красота не слетит после пересчёта и обновления.

2. Из всей сводной для отчёта вам нужны не все данные, а только конкретные модели Ford по Питеру — придётся руками фильтровать.

3. Стандартные итоги в сводной нам не подходят, т.к. нужны суммы по выручке в зелёных ячейках, но среднее по месяцу в итогах — сводная так не умеет.

4. Полученные в сводной результаты — ещё не конец, нам необходимо произвести с ними какие-то дополнительные вычисления: пересчитать выручку в тысячах, добавить прогноз на апрель, сравнить этот год с прошлым. Многое из перечисленного в сводных или невозможно в принципе, или делается весьма мучительно и долго с помощью вычисляемых полей и объектов.

5. Нужно построить по результатам хитрую диаграмму (обычные сводные диаграммы имеют много ограничений).

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

1 способ. Прямая ссылка на ячейку в сводной

Это, что называется, решение проблемы «в лоб». Сделаем на отдельном листе заготовку отчёта:

Теперь в ячейку D7 можно вручную прописать ссылку:

Где Лист1 – имя листа со сводной таблицей, а B8 – нужная нам ячейка в сводной с данными по продажам Ford Fiesta за январь.

При внешней простоте и очевидности у этого способа есть две проблемы:

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

2. Завтра, после обновления, структура сводной таблицы может измениться — например, Fiesta может оказаться уже не третьей, а седьмой строкой, Focus переехать во вторую и т.д. И тогда все ссылки придётся переделывать.

2 способ. Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

Изящным решением всех этих проблем может стать функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA), которая умеет извлекать нужные нам данные из сводной, чтобы использовать их в других таблицах или расчетах.

Чтобы её использовать, убедитесь, что при выделении любой ячейки сводной таблицы на вкладке Анализ (Analysis) или Параметры (Options) в выпадающем списке Параметры (Options) включена галочка Создать GetPivotData:

Теперь выделите первую ячейку зелёного диапазона, введите знак «равно» и щёлкните по ячейке в сводной, которая содержит нужные данные, т.е. по B8, где лежит выручка Fiesta за январь. Вместо привычной ссылки Excel вставит функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ:

(«Выручка»;’Сводная для отчета’!$A$4;

Давайте разберём её подробно:

1. Первый её аргумент («Выручка») – это имя извлекаемого поля.

2. Второй (Лист1!$A$4) — это адрес первой ячейки сводной таблицы, откуда мы берём данные. Этот параметр нужен, т.к. на листе может быть несколько сводных и Excel должен понимать, из какой именно нужно вытащить число.

3. Все остальные аргументы начиная с третьего – это попарно название поля и его значение, т.е. в нашем случае это имя модели (Наименование=»Fiesta») и временной период (Дата=1). Поскольку в сводной была применена группировка дат по месяцам, то в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ мы получили не имя месяца, а его номер. Если бы в исходной базе данных был столбец не с датой, а с названием месяца, то группировка была бы не нужна и вместо единички был бы просто «январь».

У этой функции есть несколько серьёзных преимуществ перед обычной ссылкой на B8, которая приводила бы, на первый взгляд, к тому же результату. Главный плюс в том, что если завтра после обновления в сводной таблице изменится количество строк/столбцов или Ford Fiesta станет не третьей, а пятой строкой, то нам об этом волноваться уже не придётся – функция корректно извлечёт нужное нам значение. Достаточно только обновить сводную правой кнопкой мыши – и наша красивая форма отчёта «для шефа» пересчитается автоматически.

Далее, замените в формуле «Fiesta» на $С7 (т.е. на ячейку с названием модели), а единичку на D$5 (т.е. ячейку с номером месяца) и допишите в конце формулы деление на 1000, т.к. нам нужно отобразить данные в тысячах. Затем нажмите на Enter и протяните формулу на оставшиеся зелёные ячейки.

(«Выручка»;’Сводная для отчета’!$A$4;»Наименование»;

Функция извлечёт из сводной нужные нам данные, заполнив нашу корпоративную форму отчёта.

Теперь с данными в диапазоне D7:F10 можно работать как с обычными формулами, а не как со сводной таблицей с её жёсткими ограничениями. Дальше можно спокойно считать любые итоги, динамику, прогнозы, строить любую диаграмму и т.д. Как украсить документ можно посмотреть здесь Минидиаграммы в ячейках

Формула для прогноза: =ПРЕДСКАЗ($G$5;D7:F7;$D$5:$F$5)

Формула для среднего значения: =СРЗНАЧ(D7:D10)

Формула для динамики: =D13/D14-1

Ссылка на файл в комментариях. Желаю всем без проблем сдать отчёты)) Кто знает способы лучше, можете поделиться опытом в комментариях.

Генерация красивых Excel-отчётов по шаблону

Перейти к странице

Наверняка многие из вас создавали шаблоны-парсеры. И, как правило, результаты в этом случае на выходе помещали в Excel-таблицы. Функционала ZennoPoster хватает для осуществления этих задач, за исключением несколько нюансов. Например, отсутствуют возможности для стилизации выходных таблиц, работать можно исключительно с первым листом, записывать данные можно только в строковом формате. Нет прочих различных плюшек, доступных в Excel.

Согласитесь, куда приятнее, когда на выходе получаешь вторую табличку, а не первую (см. скриншоты ниже). А если делаем парсер на заказ, а не для своих личных целей – и говорить нечего. Именно об этом и пойдёт речь в статье – как максимально просто получить красивый Excel-отчёт в ZennoPoster, с нуля до конкретного результата.

Давайте рассмотрим возможные способы реализации.

Недавно, с версии 5.10.0.0, со стороны ZennoPoster лёд чуть-чуть тронулся – появились методы для редактирования стилей ячеек через C#-сниппеты. Однако, пока сложно сказать, что это хотя бы отчасти решило проблему. Возможности самые базовые, опять же только стили, и в любом случае их надо задавать через код – для тех, кто использует чисто кубики, по сути ничего не изменилось.

Поэтому, данный способ нам не совсем подходит. Что же делать?

Думаю, многим из вас известен альтернативный метод – использование сторонней dll-библиотеки, с помощью которой можно воплотить большую часть возможностей Excel. Однако, есть одна существенная проблема – всё оформление таблиц опять же надо писать через C#-код, и даже для той сравнительно простой желанной таблички с картинки выше – его нужно написать достаточно много. Ну и конечно, для этого надо уметь в нём неплохо разбираться. На форуме, кстати, есть статья об использовании такой библиотеки, но, к сожалению, там опять же описаны только самые азы применения стилей и форматирования – те же, которые появились в ZP v5.10.

Тем не менее, мы остановимся именно на этом способе – так как у библиотеки, которую мы будем использовать (а именно известный и могучий EPPlus), есть одна шикарная фича – создание таблиц по шаблону.

Что это значит? То, что мы можем создать xlsx-файл, нарисовать в нём вручную в Excel всё оформление – а в ZennoPoster использовать его как шаблон стилей для результирующего файла. Всё, что нам останется – вставить в область данных результаты парсинга.

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

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

Собственно, рисуем в Excel заголовки и их стили, выравниваем всё, добавляем другие вещи по желанию (например, строку фильтра).

Так как стандартными средствами ZennoPoster можно работать только с первым листом Excel-файла (тоже порой весьма неприятное ограничение), рассмотрим пример с заполнением 2 листов.

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

Сохраняем файл в папке с проектом, я назвал его Template.xlsx.

2. Находим в архиве нужный файл EPPlus.dll, кидаем в папку ExternalAssemblies в директории с установленным ZennoPoster.

3. Открываем наш шаблон, добавляем блоки «Ссылки из GAC» и «Директивы using»

4. Заходим в «Ссылки из GAC» и добавляем библиотеку.

5. Заходим в «Директивы using» и вписываем туда директивы со скриншота.

Переходим к основным действиям. В первую очередь, создаём кубик C#-кода, в который поместим весь описанный ниже код.

1. Получаем внутренние таблицы проекта, предварительно заполненные нужными данными (которые спарсили/сгенерировали/получили из БД/ещё от куда-либо).

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

8. Отформатируем полученную табличку в более приглядный вид. Стили таблиц аналогичны тем, которые можно увидеть в Excel, только английскими названиями. «Light1» соответствует «Светлый1», «Medium1» – «Средний1», «Dark1» – «Темный1». Конкретно для первого листа я посчитал подходящим стиль «Светлый19».

Библиотека поддерживает большинство других формул Excel, единственное, рекомендуется их писать в английском варианте и без знака «=».

11. По аналогии с первым листом делаем форматирование области данных, теперь только уже вместе со столбцом «Всего». Дополнительно для последнего включаем особое форматирование.

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

Данный проект-пример прикрепляю во вложения к посту.

Для запуска проектов требуется программа ZennoPoster.
Это основное приложение, предназначенное для выполнения автоматизированных шаблонов действий (ботов).
Подробнее.

Для того чтобы запустить шаблон, откройте программу ZennoPoster. Нажмите кнопку «Добавить», и выберите файл проекта, который хотите запустить.
Подробнее о том, где и как выполняется проект.

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

Код ниже используем в сниппете после формирования второго листа, но до сохранения.

1. Для начала сделаем дополнительное обрамление и разделение области данных границами.

Мне больше понравился вариант со значками, поэтому я остановился на нём.

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

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

Наверняка многие из вас создавали шаблоны-парсеры. И, как правило, результаты в этом случае на выходе помещали в Excel-таблицы. Функционала ZennoPoster хватает для осуществления этих задач, за исключением несколько нюансов. Например, отсутствуют возможности для стилизации выходных таблиц, работать можно исключительно с первым листом, записывать данные можно только в строковом формате. Нет прочих различных плюшек, доступных в Excel.

Согласитесь, куда приятнее, когда на выходе получаешь вторую табличку, а не первую (см. скриншоты ниже). А если делаем парсер на заказ, а не для своих личных целей – и говорить нечего. Именно об этом и пойдёт речь в статье – как максимально просто получить красивый Excel-отчёт в ZennoPoster, с нуля до конкретного результата.

Давайте рассмотрим возможные способы реализации.

Недавно, с версии 5.10.0.0, со стороны ZennoPoster лёд чуть-чуть тронулся – появились методы для редактирования стилей ячеек через C#-сниппеты. Однако, пока сложно сказать, что это хотя бы отчасти решило проблему. Возможности самые базовые, опять же только стили, и в любом случае их надо задавать через код – для тех, кто использует чисто кубики, по сути ничего не изменилось.

Поэтому, данный способ нам не совсем подходит. Что же делать?

Думаю, многим из вас известен альтернативный метод – использование сторонней dll-библиотеки, с помощью которой можно воплотить большую часть возможностей Excel. Однако, есть одна существенная проблема – всё оформление таблиц опять же надо писать через C#-код, и даже для той сравнительно простой желанной таблички с картинки выше – его нужно написать достаточно много. Ну и конечно, для этого надо уметь в нём неплохо разбираться. На форуме, кстати, есть статья об использовании такой библиотеки, но, к сожалению, там опять же описаны только самые азы применения стилей и форматирования – те же, которые появились в ZP v5.10.

Тем не менее, мы остановимся именно на этом способе – так как у библиотеки, которую мы будем использовать (а именно известный и могучий EPPlus), есть одна шикарная фича – создание таблиц по шаблону.

Что это значит? То, что мы можем создать xlsx-файл, нарисовать в нём вручную в Excel всё оформление – а в ZennoPoster использовать его как шаблон стилей для результирующего файла. Всё, что нам останется – вставить в область данных результаты парсинга.

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

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

Собственно, рисуем в Excel заголовки и их стили, выравниваем всё, добавляем другие вещи по желанию (например, строку фильтра).

Так как стандартными средствами ZennoPoster можно работать только с первым листом Excel-файла (тоже порой весьма неприятное ограничение), рассмотрим пример с заполнением 2 листов.

Для примера нарисовал такие таблички. Также сразу добавил фильтры, закрепил области заголовков, кое-где добавил примечания. Над тематикой содержимого особо не заморачивался и взял первое что пришло в голову, соответственно, по этому поводу просьба не пинаться – всё это чисто для примера.
Посмотреть вложение 17269 Посмотреть вложение 17270

Сохраняем файл в папке с проектом, я назвал его Template.xlsx.

2. Находим в архиве нужный файл EPPlus.dll, кидаем в папку ExternalAssemblies в директории с установленным ZennoPoster.
Посмотреть вложение 17272
Посмотреть вложение 17273

3. Открываем наш шаблон, добавляем блоки «Ссылки из GAC» и «Директивы using»
Посмотреть вложение 17274

4. Заходим в «Ссылки из GAC» и добавляем библиотеку.
Посмотреть вложение 17275

5. Заходим в «Директивы using» и вписываем туда директивы со скриншота.
Посмотреть вложение 17276

Переходим к основным действиям. В первую очередь, создаём кубик C#-кода, в который поместим весь описанный ниже код.

1. Получаем внутренние таблицы проекта, предварительно заполненные нужными данными (которые спарсили/сгенерировали/получили из БД/ещё от куда-либо).

Ссылка на основную публикацию
Adblock
detector