Если (логическое выражение; значение если Истина; значение если Ложь )

Например, формула =ЕСЛИ ( G6>0;G6;0) возвращает значение из ячейки G6, если значение в ячейке G6 больше 0. В противном случае возвращает число 0.

11. Значение Пеня представим как именованную ячейку. Выделим ячейку С3 и в ыполним команду Вставка / Имя / Присвоить. Введём имя Пеня. Рассчитаем значения поля Долг+Пеня. Присвоим ячейкам поля Долг+Пеня числовой формат с двумя знаками после запятой. Для расчёта поля Долг+Пеня Активизируем ячейку I6. Введём выражение =Н6+Н6* Пеня. Значение Пеня вводить в выражение с помощью клавиши F3.

12. Значения всех полей заполненной таблицы расположим по центру с помощью кнопки панели инструментов.

13. В ячейку F18 листа Ведомость введём фразу Общая сумма долга. В ячейке I18 рассчитаем сумму по полю Долг+Пеня. Выделим ячейку I18 любым цветом.

Объединим ячейки F18:G18. Для этого выделим ячейки F18:G18. Выполним команду Формат / Ячейки… Закладка Выравнивание. Установим флажок на объединение ячеек. В ячейке I18 рассчитаем сумму по полю Долг+Пеня с помощью кнопки панели инструментов . Для этого активизируем ячейку I18, нажмём кнопку , а затем Enter. Чтобы выделить ячейку I18 каким-либо цветом,надо сделать её активной, затем воспользоваться кнопкой панели инструментов (выделение цветом). Выделить ячейку I18 любым цветом.

Сформированная таблица на листе Ведомость имеет вид:

14. Защитить лист Ведомость от изменений можно с помощью команды Сервис /Защита /Защитить лист…

15. Вставить новый лист, расположив его за листом Ведомость, назвать новый лист Рабочая ведомость. Скопировать с листа Ведомость основную таблицу (А5:I16) на лист Рабочая ведомость в ячейку А1.

Проверить правильность скопированных данных в поле Долг+Пеня. Сравнить значения полей этой и таблицы на листе Ведомость.

16. Дать имя Ведомость_список блоку ячеек А1:I12 на листе Рабочая ведомость.

17. Скопировать лист Рабочая ведомость, на вновь созданные листы Сортировка,Итоги и Автофильтр, разместив эти листы за листом Рабочая ведомость. Применить команду Правка/Переместить /Скопировать лист… В окне установить флажок на Создать копию.

 

 

Часть II

 

Сортировка списка

1. Перейдём на лист Сортировка. Отсортируем список по полю Адрес, затем по полю Наименование заказчика, затем по полю Период. В окне Сортировка сделаем установки:

Получим:

Формирование итогов

2. Перейдём на лист Итоги. Получим итоги (операция сумма) по полям Сумма к выплате,Оплачено и Долг для каждого периода. Для этого отсортируем таблицу по полю Период. Затем выполним команду

Данные/Итоги. Появится окно промежуточные итоги:

Сделаем в окне указанные установки. Нажмём ОК.

 

Получим:

 

 

При вычислении итогов таблица структурируется.

Чтобы отобразить на экране только итоговые данные, следует выполнить щелчок на кнопке второго уровня структуры, вследствие чего данные третьего уровня (исходные значения) будут скрыты. Получим:

 

Для восстановления отображения исходных значений необходимо выполнить щелчок на кнопке третьего уровня или выполнить команду Данные / Итоги, а в окне Промежуточные итоги нажать кнопку Убрать все.

 

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

 

Построение диаграмм

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

Чтобы построить круговую диаграмму, надо выделить на полученной таблице столбцы Период и Сумма к выплате.

Выполнить команду Вставка/Диаграмма или нажать кнопку Мастер диаграмм.

В появившемся окне выбрать тип диаграммы – Круговая. Нажать кнопку Далее. В следующем окне ещё раз нажать кнопку Далее. В вести название диаграммы Сумма к выплате.

Открыть закладку Подписи данных и установить флажок доли. После этого каждый сектор будет подписан своим числовым значением в процентах.

 

После нажатия в этом окне кнопки Далее появится окно, в котором указать, где будет расположена диаграмма:

Расположим полученную диаграмму на отдельном листе. Для этого установим флажок отдельном.

Лист получит имя Диаграмма 1.

Нажать кнопку Готово.

На листе Диаграмма 1 получим:

 

 

Можно изменить шрифт текста на диаграмме, предварительно выделив его. Двойной щелчок по сектору даёт возможность выдвинуть его с помощью мыши. Переименуем лист Диаграмма 1 в Круговая.

 

4. Построим гистограмму, изобразив на ней по периодам Сумму к выплате, Оплачено и Долг. Порядок созданиядиаграммы как в предыдущем задании. Однако здесь удобно скрыть столбец Разница на листе Итоги. Для этого активизируем любую ячейку этого столбца. Выполним команду Формат/Столбец/Скрыть. Выделим необходимые столбцы:

Построим гистограмму:

Чтобы получить смешанную диаграмму и представить Долг в виде линейного графика, надо щёлкнуть по одному из столбцов Долга.

 

 

Затем выполнить команду Диаграмма/Тип Диаграмм. Выбрать закладку Нестандартные, а после этого из списка выбрать График I гистограмма. Диаграмма примет вид смешанной.

Для линейного графика удобно создать дополнительную ось Y-ов справа на графике. Это тем более необходимо, если значения для линейного графика несоизмеримы со значениями других столбцов гистограммы. Щёлкнуть по линейному графику и выполнить команду Диаграмма/Параметры диаграммы. В открывшемся окне:

открыть закладку Оси и установить флажок ось Y(значений) в группе по вспомогательной оси. После этого на графике появится дополнительная ось Y-ов (справа). Нажать кнопку ОК.

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

Установим флажок значения. Нажать ОК.

Можно изменить фон диаграммы. Для этого щёлкнуть правой кнопкой мыши по полю диаграммы.

 

Появится контекстное меню:

Выбрать из списка Формат области построения…

В появившемся окне выбрать заливка – обычная.

Получим фон диаграммы - белый:

Фильтрация данных

5. Перейти на лист Автофильтр. Отфильтровать данные в поле Период по значению 1 кв и 2 кв, в поле Долг вывести значения, не равныенулю.

Сделать активной любую ячейку таблицы. Выполнить команду Данные /Фильтр/Автофильтр. У каждого столбца таблицы появится стрелка. Раскроем список в столбце Период и выберем Условие. Появится окно, в котором выполним установки:

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

 

 

После этого получим:

6. В рассматриваемых ниже примерах применим расширенный фильтр.

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

Оклад	Возраст
>10000
	>50

Если условия расположены в разных строках, то это соответствует логическому оператору ИЛИ. Если оклад больше 100000, а возраст – любой. ИЛИ если возраст больше 50, а оклад – любой, то из списка будут отобраны строки, удовлетворяющие одному из условий.

Оклад	Возраст
>10000	>50

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

Таким образом, условия фильтрации, размещённые в одной строке диапазона, объединяются логической функцией И, условия, заданные в разных строках, функцией ИЛИ. Пустая ячейка в диапазоне условий означает любые значения.

Создадим новый лист Фильтр.

Пример 1. Из таблицы Рабочая_ведомость с помощью расширенного фильтра отобрать записи, у которых Период – 1 кв и Долг+Пеня>0. Результат получить в виде таблицы на листе Фильтр:

Код заказчика

Наименование заказчика

Долг+Пеня

Шапку таблицы результата создать копированием из таблицы Рабоочая_ведомость на листе Фильтр, начиная с ячейки А5.

На листе Фильтр создадим диапазон условий в указанных ниже ячейках. Названия полей и значения периодов обязательно копировать с листа Рабочая_ведомость. Если выделяемые блоки несмежные, то при выделении применить клавишу Ctrl.

Присвоим имя этому диапазону Условие_долг.

Выполним команду Данные/Фильтр/Расширенный фильтр.

Появится диалоговое окно:

Исходный диапазон и диапазон условий задать с помощью клавиши F3.

Поместить полученные результаты на листе Фильтр в ячейку А5 ( выделить ячейки А5:С5).

 

Получим результат:

Пример 2. Из таблицы Рабочая_ведомость с помощью расширенного фильтра отобрать строки с адресом Омск за 3 кв с суммой к выплате больше 5000 и с адресом Пермь за 1 кв с любой суммой к выплате. На листе Фильтр создадим диапазон условий в указанных ниже ячейках.

Присвоим имя этому диапазону Условие_1. Названия полей и значения периодов обязательно скопировать с листа Рабочая ведомость. После выполнения команды Данные/Фильтр/Расширенный фильтр в диалоговом окне сделать следующие установки:

Получим результат:

Пример 3. Выбрать сведения о заказчиках с кодами К-155, К-347 и К-948, долг которых превышает 5000.

На листе Фильтр в ячейках H1:I4 cоздадим таблицу условий в виде списка с именем Условие_2.

 

Названия полей обязательно копировать с листа Рабочая_ведомость.

 

 

После выполнения команды Данные/Фильтр/Расширенный фильтр в диалоговом окне сделать следующие установки:

 

 

Получим результат:

Вычисляемые условия

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

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

· В строке условия вводится формула, которая вычисляет логическую константу по отношению к первой записи списка.

· В формуле указываются ссылки на ячейки списка. В левой части формулы (до знака сравнения) ссылки на ячейки списка должны быть относительными, в правой части формулы – абсолютными. Ссылки на ячейки вне списка должны быть всегда абсолютными.

Пример 4. Из таблицы на листе Рабочая ведомость отобрать строки, в которых значения Оплачено больше среднего значения по этому столбцу. Результат получить на листе Фильтр в таблице:

· На листе Фильтр создадим «шапку » таблицы-результата копированием с листа Рабочая ведомость.

· Выполним команду Окно/Новое. Затем команду Окно/Расположить.

Установим флажок слева направо. На экране появятся два окна, в первом из которых расположим лист Рабочая ведомость, а во втором – лист Фильтр. Благодаря этому удобно создавать формулу для критерия отбора на листе Фильтр.

· Сделаем активной ячейку E22 листа Фильтр, создадим в ней формулу:

· Для этого введём знак = (равно), щёлкнем по ячейке F2 на листе Рабочая ведомость

· Введём знак >(больше)

· Введём функцию СРЗНАЧ с помощью мастера функций

· В окне аргументов этой функции поместим диапазон ячеек F2:F12 (выделим его на листе Рабочая ведомость). Затем ОК.

· В ячейке E22 листа Фильтр сформируется константа Истина:

· Перейдём в любую свободную ячейку листа Фильтр и выполним команду Данные/Фильтр/Расширенный фильтр.

 

 

· В диалоговом окне сделаем установки. Для этого исходный диапазон определим клавишей F3. Для ввода диапазона условий выделим ячейки Е21:Е22 листа Фильтр. Для диапазона результата выделим ячейки А20:С20 на листе Фильтр.

 

Получим результат:

 

Сводные таблицы

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

На первом шаге выбирается источник данных. На втором шаге – диапазон исходных данных. На третьем строится макет сводной таблицы.

Макет сводной таблицы определяет её структуру:

· Страница - для размещения полей, по которым выполняется отбор записей;

· Строка – для полей, по которым выполняется группировка;

· Столбец – для размещения полей группировки;

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

Пример 1. Создадим сводную таблицу на основе таблицы листа Рабочая ведомость следующего вида:

Выполним команду Данные/ Сводная таблица… Затем Далее.

В окне указать имя Ведомость_список. (нажать F3 и выбрать Ведомость_список).

После нажатия кнопки Далее в следующем окне нажать Макет. Появится окно Мастер сводных таблиц:

В этом окне начинается построение макета сводной таблицы.

Все названия полей расположены справа в окне макета. Каждое поле можно переместить с помощью мыши в любую область макета.

При таком макете происходит размещение данных по каждому адресу на “отдельной странице”. Для отображения на экране данных о каком-либо городе нужно выбрать адрес (город) из списка.

Поля Сумма к выплате и Сумма Долг+Пеня следует разместить в область Данные. Поле Наименование заказчика в область полей строк, а Период в область полей столбцов. Т.к. в сводной таблице надо показать ещё и %задолженности, то Сумма Долг+Пеня разместим в область Данные дважды, чтобы затем преобразовать одно из них в %.

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

Откроется окно:

в окне Имя ввести Сумма к выплате. Нажать ОК. Щёлкнуть 2 раза по полю Сумма по полю Долг+Пеня и заменить его на Задолженность.

Щёлкнуть 2 раза по другому полю Сумма по полю Долг+Пеня и заменить это название на

% задолженности. В этом же окне для получения значения % нажать кнопку Дополнительно>>.

 

В окне Дополнительные вычисления открыть список и выбрать Доля от суммы по столбцу.

 

Если надо внести изменения в макет таблицы, то можно воспользоваться панелью Сводные таблицы:

Раскрыть список Сводная таблица и выбрать Мастер, а затем Макет. Илищёлкнуть правой кнопкой мыши по сводной таблице. Выбрать Мастер, а затем Макет.

Пример 2. С помощью сводной таблицы получить общие сведения о суммах выплаты и долге по каждому периоду внутри каждого города. Рассчитать % долга по каждому периоду внутри каждого города.

Создадим макет таблицы. Изменим названия данных на Сумма долга и Сумма выплат.

Нажмём ОК.

 

 

Замечание: если итоги по строкам или по столбцам выдавать не требуется, то надо войти в окно Параметры и снять флажки

Общая сумма по столбцам и Общая сумма по строкам:

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

· На панели Сводные таблицы раскрыть список, затем Формулы и Вычисляемое поле

В окне Вставка вычисляемого поля ввести имя и формулу. Нажать ОК.

В поле данных макета появится ещё одно поле.

· Изменим название поля на % долга за квартал в городе. Затем нажмём кнопку Формат.

· В следующем окне установим формат Процентный и два десятичных знака.

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

 

На основании данных сводной таблицы можно построить диаграмму.

При построении диаграмм рекомендуется:

· Удалите из таблицы промежуточные и общие итоги;

· Убедитесь, что таблица имеет не более двух полей в области столбцов и строк;

· Скройте все элементы за исключением тех, которые вас интересуют.

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

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

Пример 1.

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

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

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

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

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

Ввод формулы:

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

· Установить курсор в поле Логическое выражение и нажать красную кнопку

· Создать выражение I2=0

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

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

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

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

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

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

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

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

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

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

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

Слияние

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

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

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

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

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

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

· Создадим основной документ (например, Сообщение). Внесём в него неменяющийся текст. Изменяющаяся в дальнейшем часть документа заштрихована.

Сообщение

Адрес: ………………

 

Товарищ Антонов И.А

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

 

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

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

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

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

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

· Установить курсор в основной документ в то место, где должно располагаться 1-ое поле Адрес и нажать на панели инструментовкнопку – Вставить поля слияния. В открывшемся окне выбрать название первого поля Адрес и нажать кнопку Вставить. Закрыть окно. Эти же операции повторить для поля Наименование заказчика, для поля Долг+пеня.

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

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

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

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

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

Ø В списке значение введите М.

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

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

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

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

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

· Сохранить полученный документ под другим именем (например, Сообщение1).