6. Использование функций суммесли и счётесли

ЗАДАНИЕ 9

1. Добавьте новый лист с именем Условные операции и скопируйте на него список с листа Клиенты.

2. С помощью функций СУММЕСЛИ и СЧЁТЕСЛИ для списка на листе Условные операции подсчитайте:

1. суммарную стоимость услуг для клиентов из Минска;

2. количество клиентов из Минска.

Порядок выполнения:

1. Добавьте новый лист с именем Условные операции и скопируйте на него список с листа Клиенты.

2. На листе Условные операции:

1. В ячейку A13 введите заголовок Стоимость услуг для клиентов из Минска.

Для подсчета суммарной стоимости услуг для клиентов из Минска установите курсор в ячейку E13 и вставьте функцию CУММЕСЛИ (командой Формулы – Библиотека функций:  Вставить функцию).

В диалоговом окне Аргументы функции СУММЕСЛИ:

– в поле Диапазон укажите C2:C11 (выделением мышью полей Адрес);

– в поле Критерий введите *минск* ;

– в поле Диапазон_суммирования укажите диапазон E2:E11 (выделением мышью полей Стоимость).

2. В ячейку A14 введите заголовок Количество клиентов из Минска.

Для подсчета количества клиентов из Минска установите курсор в ячейку C14 и вставьте функцию CЧЁТЕСЛИ (командой Формулы – Библиотека функций:  Вставить функцию).

В диалоговом окне Аргументы функции СЧЁТЕСЛИ:

– в поле Диапазон укажите C2:C11 (выделением мышью полей Адрес);

– в поле Критерий введите *минск* .

* ЗАДАНИЕ 10

С помощью функций СУММЕСЛИ и СЧЁТЕСЛИ для списка на листе Условные операции подсчитайте:

1. суммарную стоимость услуг стоимостью более 20 000р.;

2. количество услуг с датой оплаты до 2005 г.

7. Использование условного форматирования

ЗАДАНИЕ 11

С помощью условного форматирования для списка на листе Условные операции:

1. ячейки со стоимостью меньше 10 000р. выделите зеленым цветом текста, а больше 20 000р. – красным;

2. строки с датой оплаты больше 30 дней после даты обращения выделите голубым фоном, кроме столбца Стоимость.

Порядок выполнения:

1. На листе Условные операции выделите диапазон ячеек со стоимостью (E2:E11) и выполните команду Главная – Стили: Условное форматирование – Создать правило… – Форматировать только ячейки, которые содержат.

В диалоговом окне Создание правила форматирования:

• в области Выберите тип правила: выберите Форматировать только ячейки, которые содержат;

• в области Измените описание правила:

– выберите из списка режим Значение ячейки, операцию меньше и введите значение 10 000;

– нажмите кнопку Формат… и в диалоговом окне Формат ячеек на вкладке Шрифт выберите в списке Цвет: зеленый цвет и нажмите кнопку ОК и еще раз ОК;

Для добавления второго условия для выделенного диапазона ячеек со стоимостью (E2:E11), выполните снова команду Главная – Стили: Условное форматирование – Создать правило… – Форматировать только ячейки, которые содержат.

В диалоговом окне Создание правила форматирования:

• в области Выберите тип правила: выберите Форматировать только ячейки, которые содержат;

• в области Измените описание правила:

– выберите из списка режим Значение ячейки, операцию больше и введите значение 20 000;

– нажмите кнопку Формат… и в диалоговом окне Формат ячеек на вкладке Шрифт выберите в списке Цвет: красный цвет и нажмите кнопку ОК и еще раз ОК.

Для проверки работы установленного условного форматирования введите в ячейку E5 значение меньше 10 000, затем больше 20 000.

2. Выделите несмежные диапазоны всех ячеек с данными кроме столбца Стоимость (A2:D11; F2:F11) и выполните команду Главная – Стили: Условное форматирование – Создать правило… – Использовать формулу для определения форматируемых ячеек.

В диалоговом окне Создание правила форматирования:

• в области Измените описание правила:

– введите формулу¹: =$F2-$A2>30

– нажмите кнопку Формат… и в диалоговом окне Формат ячеек на вкладке Заливка выберите в области  Цвет фона: голубой цвет фона ячеек и нажмите кнопку ОК и еще раз ОК.

Для проверки работы установленного условного форматирования введите в ячейку F5 значение 29.03.04.

* ЗАДАНИЕ 12

С помощью условного форматирования для списка на листе Условные операции:

1. ячейки со стоимостью от 10 000р. до 20 000р. выделите синим цветом текста;

2. фамилии клиентов с услугой Консультация выделите голубым фоном, а фамилии клиентов с услугами стоимостью более 20 000р. – зеленым фоном.