Группировка элементов по временным диапазонам

Мастер сводных таблиц автоматически группирует элементы внутреннего поля для каждого заголовка, создаёт промежуточные итоги для каждой группы. Но иногда необходимо группировать, например, месячные элементы в квартальные группы или даты в месяц и т.д. Рассмотрим группировку на примере Рабочей ведомости.

Пример 3. Скопируем лист Рабочая ведомость на лист Группировка. Добавим столбцы Дата и Месяц в конце таблицы и заполним их данными (смотри таблицу ниже). Столбец Период удалим.

На основе полученной таблицы создадим сводную таблицу с получением сумм Сумма к выплате и Оплачено по месяцам и заказчикам.

Создадим макет сводной таблицы:

Фрагмент таблицы выглядит так:

Выделим ячейку с любой датой и выполним команду Группа и структура/Группировать.

В появившемся окне Группирование никаких установок в этом окне делать не надо. Нажать ОК. Получим сводную таблицу по месяцам.

 

В полученной сводной таблице данные каждого месяца группируются и суммируются по каждому заказчику.

Пример 4. На основе исходной таблицы, скопированной на лист Группировка, создадим сводную таблицу, в которой по месяцам выведем Сумму к выплате и Оплачено. Сгруппируем данные по месяцам с шагом 3 – по кварталам.

Создадим макет для сводной таблицы:

 

Получим сводную таблицу:

Выделим ячейку в столбце месяц на сводной таблице и выполним команду Группа и структура/Группировать. В появившемся окне Группирование установим шаг 3 в окне с шагом:

 

После нажатия ОК получим:

По каждому заказчику вычислены Сумма к выплате и Оплачено за каждый квартал, т.е. за каждые три месяца.

Использование вложенных функций ЕСЛИ

Создадим копию листа Рабочая ведомость на листе Если. В таблицу добавим столбец Характер долга.

Пример 1.

Заполним столбец Характер долга с учетом указанных условий: если Долг+Пеня=0, то выдать сообщение «Долга нет». В противном случае, если 0< Долг+Пеня<= 2000, то выдать «Долг небольшой». В противном случае, если 2000<Долг+Пеня<= 8000, то выдать «Долг большой». В остальных случаях выдать «Долг очень большой».

Установить курсор в ячейку J2 и создать формулу:

=ЕСЛИ(I2=0;"Долга нет";ЕСЛИ(И(I2>0;I2<=2000);

"Долг небольшой";ЕСЛИ(И(I2>2000;I2<=8000);"Долг большой";

"Долг очень большой")))

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

· Выбрать функцию ЕСЛИ из списка встроенных функций

· В появившемся окне установить курсор в поле Логическое выражение и нажать красную кнопку для свёртывания окна

· Создать выражение I2=0 (щёлкнуть по ячейке I2, знак равно и нуль набрать вручную)

· Установить курсор в поле Значение_если _истина и ввести Долга нет

· Установить курсор в поле Значение_если_ложь и раскрыть список рядом с функцией Если

 

Выбрать функцию Если и заполнить аналогично параметры нового окна:

· Установить курсор в поле Логическое выражение, раскрыть список функций, показанный выше. Выбратьиз него функцию И. Создать выражение И(I2>0;I2<=2000).

· Установить курсор в поле Значение_если _истина и ввести Долг небольшой.

· Установить курсор в поле Значение_если_ложь, раскрыть список функций.

· Выбрать из него функцию Если, заполнить параметры нового окна функции Если.

· Установить курсор в поле Логическое выражение и создать выражение И(I2>2000;I2<=8000).

· Установить курсор в поле Значение_если _истина и ввести Долг большой.

· Установить курсор в поле Значение_если_ложь и ввести Долг очень большой. Получим:

 

Функции И и ИЛИ позволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения: =, >, <, >=, <=, <>. Эти функции имеют одинаковые аргументы, но результаты получаются различными. Например, И(I2>2000;I2<=8000) предполагает выполнение двух условий, а ИЛИ(I2>2000;I2<=8000) только какого-либо одного. Таким образом, функция ИЛИ возвращает значение ИСТИНА, если хотя бы одно из логических выражений истинно. Функция И возвращает значение ИСТИНА, только если все логические выражения истинны.

Слияние

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

Операцию можно выполнять, используя Мастер слияния. Рассмотрим слияние на результатах примера листа Если.

Пример. Разослать письма с уведомлением тем заказчикам, у которых есть очень большой долг.

Для этого сначала с помощью Автофильтра отберем из таблицы тех заказчиков, которые имеют такой долг. Получим таблицу данных:

Источником данных для слияния служит таблица с отобранными заказчиками. Он подготовлен в Excel. Скопируем источник данных на лист Слияние. Сохраним файл под именем Учебная задача и закроем его.

Порядок создания писем:

· Создать в Word основной документ (например, Cообщение). Внести в него постоянный текст.

 

Сообщение

Адрес:

 

Товарищ

Предлагаем немедленно погасить задолженность сумме

 

Бухгалтер Петров В.В.

Сохраним документ в своей папке, под именем Сообщение. Документ Сообщение не закрывать.

· Для выбора типа основного документа на панели инструментов Слияние нажать кнопку – Выбрать тип документа. Откроется окно Тип основного документа. Установить флажок Письма. Нажать ОК.

Замечание: Если панель инструментов Слияние отсутствует, тоеёнадо открыть с помощью команды Вид/Панели инструментов/Слияние.

· Для подключения к источнику данных нажать на панели инструментов кнопку – Открыть источник данных. В окне Выбор источника данных найти файл Учебная задача, который является источником данных. Выделить найденный файл Учебная задача. Нажать кнопку Открыть.

· В следующем окне выделить лист Слияние источника данных и нажать ОК.

· Установить курсор в основной документ после слова Адрес и нажать на панели инструментовкнопку – Вставить поля слияния. В открывшемся окне Добавить поля слияния, снять флажок с Поля адреса, выбрать название первого поля Адрес и нажать кнопку Вставить. Закрыть окно.

Эти же операции повторить для полей Наименование заказчика и поля Долг+пеня.

· После того, как вставлены все поля, на панели инструментов нажать кнопку – Поля/данные. Заполнится данными одно письмо.С помощью кнопок Перейти к записи можно просмотреть другие письма.

· Для создания всех писем, надо выполнить слияние с помощью кнопки – Слияние в новый документ. Откроется окно Составные новые документы. Установить флажок на Все и нажать ОК. Все письма создаются на отдельных страницах. Их можно просмотреть.

· Полученные письма распечатать.

Примечание: При создании писем иногда требуется изменять обращение к клиенту в зависимости от его пола: Уважаемый господин или Уважаемая госпожа. Для автоматизации этого необходимо присутствие пола клиента в источнике данных (в нашем примере его нет). Можно добавить столбец, заполненный соответственно м или ж.

Для изменения обращения необходимо:

· Установить курсор в начало той строки, в которой должно быть обращение к клиенту.

· Нажать кнопку Добавить поле Word на панели Слияние и выбрать из списка строку IF…THEN…ELSE.

· В появившемся диалоговом окне в списке поле выбрать слово “Пол”.

· В списке оператор выбрать слово Равно.

· В списке значение введите м.

· В окне Вставить следующий текст: Уважаемый господин.

· В окне В противном случае вставить следующий текст: Уважаемая госпожа.

· Нажать кнопку ОК