Как сделать саммари в excel
Обновлено: 04.07.2024
Для автоматизации повторяющихся задач в Microsoft Excel можно быстро записать макрос. Предположим, у вас есть даты в различном формате и вы хотите применить ко всем из них один формат. Это можно сделать с помощью макроса. Вы можете записать макрос, который применяет нужный формат, а затем запускать его при необходимости.
При записи макроса все действия в записи макроса записываются Visual Basic для приложений (VBA) коде. Эти действия могут включать ввод текста или чисел, выбор ячеек или команд на ленте или в меню, форматирование ячеек, строк или столбцов или даже импорт данных из внешнего источника, скажем, Microsoft Access. Visual Basic Приложение (VBA) — это подмножество мощного Visual Basic программирования, которое входит в большинство Office приложений. Хотя VBA позволяет автоматизировать процессы как в приложениях, так и между Office, необязательно знать код VBA или программирование на компьютере, если оно делает то, что вам нужно.
Важно знать, что при записи макроса регистрируются почти все ваши действия. Поэтому если вы допустите ошибку, например нажмете не ту кнопку, средство записи макросов зарегистрирует это действие. В таком случае можно снова записать всю последовательность или изменить код VBA. Поэтому перед записью процесса следует хорошо проработать его. Чем точнее вы запишете последовательность, тем более эффективно будет работать макрос.
Макросы и средства VBA находятся на вкладке Разработчик, которая по умолчанию скрыта, поэтому сначала нужно включить ее. Дополнительные сведения см. в статье Отображение вкладки "Разработчик".
Запись макроса
Перед записью макросов полезно знать следующее:
Макрос, записанный для работы с диапазоном Excel, будет выполняться только для ячеек этого диапазона. Поэтому если вы добавите в диапазон новую строку, макрос не будет применяться к ней.
Если вам нужно записать длинную последовательность задач, советуем вместо этого использовать несколько более мелких макросов.
В макросе могут содержаться и задачи, не относящиеся к Excel. Процесс макроса может охватывать прочие приложения Office и другие программы, которые поддерживают Visual Basic для приложений (VBA). Например, вы можете записать макрос, который сначала обновляет таблицу в Excel, а затем открывает Outlook для ее отправки по электронной почте.
Чтобы записать макрос, следуйте инструкциям ниже.
На вкладке Разработчик в группе Код нажмите кнопку Запись макроса.
Нажмите ALT+T+M+R.
В поле Имя макроса введите название макроса. Сделайте имя понятным, чтобы можно было быстро найти нужный макрос.
Чтобы назначить сочетание клавиш для запуска макроса, в поле Сочетание клавиш введите любую строчную или прописную букву. Рекомендуется использовать сочетания клавиш с CTRL+SHIFT, так как они будут заменять собой совпадающие с ними стандартные сочетания клавиш в Excel, пока открыта книга, содержащая макрос. Например, если назначить сочетание клавиш CTRL+Z (Отменить), вы не сможете использовать его для функции "Отменить" в данном экземпляре Excel.
В списке Сохранить в выберите книгу, в которой вы хотите сохранить макрос.
Как правило, макрос сохраняется в расположении Эта книга, но если вы хотите, чтобы макрос был доступен при использовании Excel, выберите Личная книга макроса . При выборе личнойкниги макроса Excel создает скрытую личную книгу макроса (Personal.xlsб), если она еще не существует, и сохраняет макрос в этой книге.
В поле Описание при необходимости введите краткое описание действий макроса.
Хотя поле "Описание" является необязательным, рекомендуется его заполнить. Кроме того, желательно ввести понятное описание, которое будет полезно вам и всем, кто запускает макрос. Если у вас много макросов, описания помогут быстро определить, для чего они нужны.
Чтобы начать запись макроса, нажмите кнопку ОК.
Выполните действия, которые нужно записать.
На вкладке Разработчик в группе Код нажмите кнопку Остановить запись .
Нажмите ALT+T+M+R.
Работа с макросами, записанными в Excel
На вкладке Разработчик щелкните Макросы, чтобы просмотреть макросы, связанные с книгой. Кроме того, можно нажать клавиши ALT+F8. При этом откроется диалоговое окно Макрос.
Внимание: Макросы нельзя отменить. Прежде чем впервые запускать записанный макрос, сохраните книгу или создайте ее копию, чтобы предотвратить внесение нежелательных изменений. Если вас не устраивают результаты выполнения макроса, вы можете закрыть книгу, не сохраняя ее.
Ниже приведены дополнительные сведения о работе с макросами в Excel.
Сведения о параметрах безопасности макросов и их значении.
Макросы можно запускать различными способами, например с помощью сочетания клавиш, графического объекта, панели быстрого доступа, кнопки или даже при открытии книги.
С помощью редактора Visual Basic можно изменять макросы, присоединенные к книге.
Если книга содержит макрос VBA, который нужно использовать где-либо еще, этот модуль можно скопировать в другую книгу с помощью редактора Microsoft Visual Basic.
Назначение макроса объекту, фигуре или графическому элементу
Щелкните правой кнопкой мыши объект, рисунок, фигуру или элемент, которому вы хотите назначить существующий макрос, и выберите пункт Назначить макрос.
В поле Назначить макроса выберите макрос, который вы хотите назначить.
Вы можете назначить макрос значку и добавить его на панель быстрого доступа или ленту.
Вы можете назначать макросы формам и элементам ActiveX на листе.
Узнайте, как включать и отключать макросы в файлах Office.
Открытие редактора Visual Basic
Нажмите клавиши ALT+F11.
Узнайте, как найти справку по элементам Visual Basic.
Работа с записанным кодом в редакторе Visual Basic (VBE)
С помощью редактора Visual Basic (VBE) вы можете добавлять в записанный код собственные переменные, управляющие структуры и другие элементы, которые не поддерживает средство записи макросов. Так как средство записи макросов фиксирует почти каждый шаг, выполняемый во время записи, может также потребоваться удалить ненужный код. Просмотр записанного кода — отличный способ научиться программировать на VBA или отточить свои навыки.
Пример изменения записанного кода можно найти в статье Начало работы с VBA в Excel.
Запись макроса
Перед записью макросов полезно знать следующее:
Макрос, записанный для работы с диапазоном Excel, будет выполняться только для ячеек этого диапазона. Поэтому если вы добавите в диапазон новую строку, макрос не будет применяться к ней.
Если вам нужно записать длинную последовательность задач, советуем вместо этого использовать несколько более мелких макросов.
В макросе могут содержаться и задачи, не относящиеся к Excel. Процесс макроса может охватывать прочие приложения Office и другие программы, которые поддерживают Visual Basic для приложений (VBA). Например, вы можете записать макрос, который сначала обновляет таблицу в Excel, а затем открывает Outlook для ее отправки по электронной почте.
Макросы и средства VBA находятся на вкладке Разработчик, которая по умолчанию скрыта, поэтому сначала нужно включить ее.
Перейдите в Excel > параметры. > ленты & панель инструментов.
В категории Настроить ленту в списке Основные вкладки установите флажок Разработчик, а затем нажмите кнопку Сохранить.
Чтобы записать макрос, следуйте инструкциям ниже.
На вкладке Разработчик нажмите кнопку Запись макроса.
В поле Имя макроса введите название макроса. Сделайте имя понятным, чтобы можно было быстро найти нужный макрос.
В списке Сохранить в выберите книгу, в которой вы хотите сохранить макрос.
Как правило, макрос сохраняется в расположении Эта книга, но если вы хотите, чтобы макрос был доступен при использовании Excel, выберите Личная книга макроса. При выборе личнойкниги макроса Excel создает скрытую личную книгу макроса (PERSONAL.XLSB), если она еще не существует, и сохраняет макрос в этой книге. Книги в этой папке открываются автоматически при Excel, и любой код, сохраненный в личной книге макроса, будет указан в диалоговом окну Макрос, которое объясняется в следующем разделе.
Чтобы назначить сочетание клавиш для запуска макроса, в поле Сочетание клавиш введите любую строчную или прописную букву. Рекомендуется использовать сочетания клавиш, которые еще не назначены другим командам, так как они будут переопределять совпадающие с ними стандартные сочетания клавиш в Excel, пока открыта книга, содержащая макрос.
В поле Описание при необходимости введите краткое описание действий макроса.
Хотя поле "Описание" является необязательным, рекомендуется его заполнить. Полезно ввести содержательное описание со всеми сведениями, которые могут быть полезны вам или другим пользователям, которые будут запускать макрос. Если у вас много макросов, описания помогут быстро определить, для чего они нужны.
Чтобы начать запись макроса, нажмите кнопку ОК.
Выполните действия, которые нужно записать.
На вкладке Разработчик щелкните Остановить запись.
Работа с макросами, записанными в Excel
На вкладке Разработчик щелкните Макросы, чтобы просмотреть макросы, связанные с книгой. При этом откроется диалоговое окно Макрос.
Примечание: Макросы нельзя отменить. Прежде чем впервые запускать записанный макрос, сохраните книгу или создайте ее копию, чтобы предотвратить внесение нежелательных изменений. Если вас не устраивают результаты выполнения макроса, вы можете закрыть книгу, не сохраняя ее.
Ниже приведены дополнительные сведения о работе с макросами в Excel.
Узнайте, как включать и отключать макросы в Excel для Mac.
Если книга содержит макрос VBA, который нужно использовать где-либо еще, этот модуль можно скопировать в другую книгу с помощью редактора Microsoft Visual Basic.
Назначение макроса объекту, фигуре или графическому элементу
Щелкните правой кнопкой мыши объект, рисунок, фигуру или элемент, которому вы хотите назначить существующий макрос, и выберите пункт Назначить макрос.
В поле Назначить макроса выберите макрос, который вы хотите назначить.
Вы можете назначить макрос значку и добавить его на панель быстрого доступа или ленту.
Вы можете назначать макросы формам и элементам ActiveX на листе.
Открытие редактора Visual Basic
На вкладке Разработчик щелкните Visual Basic или выберите Сервис > Макрос > Редактор Visual Basic.
Узнайте, как найти справку по элементам Visual Basic.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
In this video I want to show you how to build a quick summary table using the COUNTIF and SUMIF functions.
Here we have a sample set of data that shows t-shirt sales.
You can see we have columns for date, item, color, and amount.
So let's break this data down by color.
Now, before we start, I want to mention that Pivot Tables would be an excellent way to summarize this data, but you can certainly use formulas for basic summaries, and that's what I'm going to demonstrate here.
First, I'm going to name the Color and Amount columns in the data. Now, this isn't necessary, but naming these ranges means I won't need to use absolute addresses later, and it will make the formulas really short and easy to read.
Now, in our summary table, we need a list of unique colors. To build this list, I'll copy the full list, then use the Remove Duplicates command in Excel.
If you just have a few items in a list, there's no need to use Remove Duplicates. But it's a great way to build a clean list of unique values when you're working with unfamiliar data.
Now I'll add the first formula. COUNTIF needs a range and a criteria. Since we're counting colors, the range is the color column.
Next, we need a criteria, and that's where our list of colors comes in. We already have the colors in a list in our table, so I can just point to that column and pick up the reference. When I hit Enter, and copy the formula down, we have a count for each color.
Now let's extend this summary table to include amounts.
In this case, we'll need to use the SUMIF function. As before, I need to provide Color as the range, then pick up the name of the color as a cell reference in our table.
Then we need to provide the range to sum, which is the Amounts column.
When I copy the formula down the table, we have a breakdown of amount by color.
Now, to finish things off, I can copy formatting from the source table to the summary table using Paste Special.
So there you have it; we have our summary table. And, if I change some of the source data, you'll see the summary table update instantly.
SUMIFS like other …IF or …IFS function are great tools to aggregate data based on a set of conditions. The downside of this approach is that the criteria must be supplied manually, especially when you need to create a summary table. In this guide, we’re going to show you how to use UNIQUE and SUMIFS functions in combination to generate an Excel summary table.
A summary table should include a unique list of categories. Creating a unique list of categories can become tedious as you keep adding more items in the future. To keep things simple and automate this task, you essentially can use either one of the two methods: Pivot Table or Excel formulas. Let's take a look at both.
Formula approach
Although Excel has a built-in feature for creating unique lists (or rather removing duplicate values), this actually requires a more manual approach. There were no dedicated formulas or tools since 2018 for creating a unique list of values. Traditionally, you had to use complex formulas to get a list of unique items.
Fortunately, Microsoft has introduced the UNIQUE formula in the later versions, which makes things much easier and automates most of this process. All you need is to do is to supply the reference of categories in your data. Excel will populate the unique list of values automatically.
Pivot Table Approach
An alternative way to creating an Excel summary table is using a PivotTable. A PivotTable automatically creates a unique list of category items and aggregates the data. The approach is simple:
В данном материале мы пройдемся по наиболее полезным функциям, которые нам предоставляет связка pandas и XlsxWriter для записи данных.
Для начала загружаем зависимости и создаём DataFrame:
Сохранение данных с использованием библиотеки XlsxWriter следует проводить одним из следующих образов:
Используя первый способ данные просто сохраняются в файл table.xlsx с использованием движка XlsxWriter (требует, чтобы был установлен соответствующий пакет). В целом, когда нам не требуется применять форматирование, параметр engine можно и опустить.
Во втором случае, помимо того, что мы имеем возможность сохранить несколько DataFrame на одном или нескольких листах, так же возможно добавить ячейкам форматирование, вставить графики и специализированные таблицы.
Автофильтрация
Наиболее простой в реализации функцией форматирования будет добавления автофильтров. Для этого на соответствующем листе следует вызвать метод autofilter и указать диапазон применения фильтрования:
Возможно применение и индексной нотации:
(Более подробно о autofilter по ссылке )
По документации требуется указывать полностью диапазон ячеек, использующихся в автофильтре, но в реальности excel корректно применяет фильтр даже когда указан только диапазон колонок, что несколько упрощает работу. Например, как в следующем случае:
Настройка размеров ячеек
Изначально XlsxWriter предоставляет нам инструменты для установки высоты и ширины как для целых строк и столбцов, так и для их диапазонов, с некоторой оговоркой.
Чтобы установить высоту одной строки следует использовать метод:
где 0 – индекс строки, 20 – высота строки.
Для установки высоты нескольких строк потребуется провести итерацию по всем нужным строкам, или же можно установить значение высоты строки по умолчанию для всего документа:
Для установки ширины столбца есть такой метод:
Важно заметить, что хоть официальная документация и утверждает, что при настройке ширины столбцов не должно быть пересекающихся диапазонов, однако следующий код прекрасно работает:
Установка значения по умолчанию для ширины столбцов не предусмотрена автором библиотеки. Так же библиотека не предусматривает инструмента для определения автоматической ширины или высоты ячеек, приходится мириться с этим неудобством и искать похожие по функциям обходные решения на форумах (например, тут).
Форматирование текста
Форматирование текста, такое как изменение размера, шрифта, цвета и т.д. так же делается с использованием уже известных нам функций: set_column и set_row.
В результате получаем следующий файл:
Из таблицы виден важный факт:
стиль ячеек не может быть перезаписан. На строку с заголовками pandas уже применил форматирование, таким образов мы на него уже воздействовать никак не можем. Аналогично со строкой 2, ячейки которой по идее должны были окраситься в синий и зеленый цвета, однако этого не произошло.
Если есть сильное желание придать свой собственный формат строке с заголовками таблицы, то можно сделать так:
Добавление графиков
Помимо чистых цифр, бывает полезно добавить в таблицу некоторую сопровождающую информацию, например графики. Они могут быть сгенерированы средствами Excel или же как обыкновенное сгенерированное изображение.
Добавление сгенерированного изображения максимально просто и понятно.
- Создаём изображение
- Сохраняем его как файл
- Указываем полный или относительный путь к файлу изображения, ячейку, в которую хотим поместить изображение и дополнительные опции, если нужно (например отступ от края ячейки, масштабирование ширины, высоты изображения и т.д.. Подробнее о списке опций по ссылке
Пример добавления изображения в документ:
С другой стороны, для добавления графиков используется непосредственно средствами библиотеки XlsxWriter метод add_chart объекта типа worksheet, в параметрах которого можно указать тип графика (pie в данном случае). После этого нужно заполнить списки категорий и значений через метод add_series. Данный метод принимает ссылки в буквенной и в численной нотации.
Перед добавлением графика на лист, можно дополнительно отформатировать внешний вид легенды, добавить ещё данных на ту же область, добавить названия осей и т.д. Под конец необходимо добавить график на лист вызовом метода insert_chart. Более подробно о работе с графиками в XlsxWriter можно почитать по ссылке
Мы рассмотрели некоторые возможности, которые предоставляет нам связка библиотек Pandas и XlsxWriter. Их очень легко и удобно встраивать в собственные проекты. Использование средств, описанных в данной статье, не исчерпывают все возможности этих библиотек, но даже с этим скромным инструментарием в кармане, вы можете сделать ваши выгрузки намного более информативными и приятными глазу.
Читайте также: