У Митчелла есть много данных в рабочем листе, который представляет все http://www.formville.com/C17_free-purchase-orders.html [заказы на закупку] его компании за год. Данные сортируются по столбцу C, который содержит названия поставщиков. Митчелл хочет напечатать отдельную страницу для каждого поставщика со всеми данными для этих строк. Он задается вопросом, есть ли способ автоматизировать печать листов конкретного поставщика.

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

Использование промежуточных итогов

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

  1. Откройте вкладку «Данные» на ленте.

  2. В группе «Структура» щелкните инструмент «Промежуточный итог». Excel отображает диалоговое окно «Промежуточный итог». (См. Рис. 1.)

  3. Убедитесь, что в раскрывающемся списке «При каждом изменении в» установлено значение «Поставщик». (Используйте имя столбца C.) Это указывает, куда Excel будет вставлять промежуточные итоги.

  4. В раскрывающемся списке Use Function должно быть установлено значение Count.

  5. Используя список в поле «Добавить промежуточный итог в», выберите столбец «Поставщик» (столбец C). Здесь будет добавлен счетчик.

  6. Убедитесь, что установлен флажок «Заменить текущие промежуточные итоги».

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

  8. Убедитесь, что установлен флажок Сводка ниже данных.

  9. Щелкните ОК.

Excel помещает промежуточные итоги на ваш лист, но он также должен размещать разрывы страниц перед каждым новым поставщиком. (Это из-за шага 7, описанного выше.) Разрывы страниц могут быть не сразу заметны, но они проявляются при печати рабочего листа.

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

Использование отфильтрованных данных

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

(В этом случае вам нужно будет вручную выбрать все свои данные.) Затем выполните следующие действия:

  1. Откройте вкладку «Данные» на ленте.

  2. Щелкните инструмент «Фильтр» в группе «Сортировка и фильтр». Excel должен отображать раскрывающиеся индикаторы Автофильтра рядом с каждой меткой столбца в строке 1.

  3. Используя индикатор раскрывающегося списка для столбца «Поставщик» (столбец C), выберите имя поставщика, которого вы хотите распечатать. Ваш список автоматически фильтруется, чтобы отображать только заказы на покупку от этого поставщика.

  4. Распечатайте страницу как обычно. В распечатанном отчете должны отображаться только заказы на закупку для поставщика, указанного на шаге 3.

Если вы хотите распечатать отчеты для других поставщиков, все, что вам нужно сделать, это изменить фильтр (шаг 3) и перепечатать (шаг 4). Когда вы закончите, вы можете удалить фильтр, снова щелкнув инструмент «Фильтр» на вкладке «Данные» ленты.

Использование сводных таблиц

Еще один быстрый способ создания нужных отчетов — использовать возможности сводной таблицы Excel. Я не буду вдаваться в подробности о том, как создать сводную таблицу здесь, поскольку это было рассмотрено в других вопросах ExcelTips. Сводную таблицу можно настроить практически любым способом, но необходимо убедиться, что поле «Поставщик» находится в группе «Фильтры» на панели «Поля сводной таблицы». (См. Рис. 2.)

image

Рисунок 2. Настройка сводной таблицы.

Затем откройте вкладку «Параметры» или «Анализ» на ленте, в зависимости от вашей версии Excel. (Эти вкладки отображаются только при выборе ячейки в сводной таблице.) В группе «Сводная таблица» слева от ленты щелкните раскрывающийся список «Параметры» и выберите «Показать страницы фильтров отчетов». (Этот параметр доступен только в том случае, если вы убедились, что поле «Поставщик» находится в группе «Фильтры», как упоминалось ранее.) Excel отображает диалоговое окно «Показать страницы фильтров». (См. Рис. 3.)

image

Рисунок 3. Диалоговое окно Показать страницы фильтров отчетов.

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

Использование макросов

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

Sub CreateVendorSheets()

' To use this macro, select the first cell in     ' the column that contains the vendor names.



Dim sTemp As String     Dim sVendors(99) As String     Dim iVendorCounts(99) As Integer     Dim iVendors As Integer     Dim rVendorRange As Range     Dim c As Range     Dim J As Integer     Dim bFound As Boolean

' Find last row in the worksheet     Set rVendorRange = ActiveSheet.Range(Selection, _       ActiveSheet.Cells(Selection.SpecialCells(xlCellTypeLastCell).Row, _       Selection.Column))



' Collecting all the vendor names in use     iVendors = 0

For Each c In rVendorRange         bFound = False         sTemp = Trim(c)

If sTemp > "" Then             For J = 1 To iVendors                 If sTemp = sVendors(J) Then bFound = True             Next J             If Not bFound Then                 iVendors = iVendors + 1                 sVendors(iVendors) = sTemp                 iVendorCounts(iVendors) = 0             End If         End If     Next c

' Create worksheets     For J = 1 To iVendors         Worksheets.Add After:=Worksheets(Worksheets.Count)

ActiveSheet.Name = sVendors(J)

Next J

' Start copying information     Application.ScreenUpdating = False     For Each c In rVendorRange         sTemp = Trim(c)

If sTemp > "" Then             For J = 1 To iVendors                 If sTemp = sVendors(J) Then                     iVendorCounts(J) = iVendorCounts(J) + 1                     c.EntireRow.Copy Sheets(sVendors(J)). _                       Cells(iVendorCounts(J), 1)

End If             Next J         End If     Next c     Application.ScreenUpdating = True End Sub

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

_Примечание: _

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

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (13633) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.