V. Задания на списки

1. Скопировать из файла "данные для примерного варианта" в свой файл лист "сделки"

2. Поименовать таблицу листа «сделки», назвав ее сегодняшней датой

3. При помощи функции ВПР к этой таблице справа около столбца “CompanyName” добавить колонку с датой создания фирмы (“date of establishment”).

4. Отсортировать таблицу по 4-м ключам: OrderDate по возрастанию (от старых к новым), CompanyName по алфавиту, Quantity по убыванию, Subtotal по убыванию.

5. При помощи функции базы данных определить количество сделок в 1996 году, в которых скидка была, нулевой или 25%.

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

7. Рассчитать среднюю цену сделки (the transaction price) среди сделок французских и немецких фирм, заключенных с августа 1996г. по март 1997г.

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

9. На листе "banks" построить нижеприведенную таблицу (рис. 69). Для этого

a. При помощи обычного фильтра отобрать банки, изменение в рейтинге которых превышает 100 единиц. Скопировать названия отобранных банков и расположить их в строке (можно использовать транспонирование).

b. Названия трех показателей расположить в строках в том порядке, в котором они расположены в формируемой таблице (рис. 69).

c. Используя функцию ПОИСКПОЗ, найти номера строк внутри таблицы, в которых расположена информация по отобранным банкам.

d. При помощи функции ГПР отобрать в таблицу заданные показатели, используя найденные номера строк с данными по отобранным банкам.

Перенести лист сделки, как в исходном задании

Удалить из таблицы пустые столбцы, встать в клетку таблицы, нажать CTRL+A, в левой верхней клетке рядом со строкой формулы написать сегодняшнуюю дату в формате October_30 (главное, чтобы начиналось с буквы), и ENTER

Выделить стобцы HI (последние в таблице) и поименовать их в том же окошке, допустим, table

Вставить пустой столбец правой кнопкой мыши нажав на Company Name

В первой строчке этого столбца (F1) записать формулу =ВПР(E2;table;2;0) и протянуть до конца

Затем встать в любую клетку->данные->сортировка, значок в виде таблички.

Сортировка должна выглядеть как на фото

САМОЕ ГЛАВНОЕ: критерии базы данных

То, что в условии дано в виде «или»(как здесь скидка), записывается в виде двух строчек. То есть если дата в 1996, то мы ее будем повторять для всех, так как дата – единственный критерий, а вот скидки 2, значит в каждой строчке мы меняем это Условие. Главное, чтобы были все возможные комбинации критериев.

Даты можно было бы записать как >=01.01.1996 <=31.12.1996

OrderDate	OrderDate	Discount
>31.12.1995	<01.01.1997	0,00
>31.12.1995	<01.01.1997	0,25

Теперь пишем где-нибудь рядом =БСЧЁТ(october_24;;A1:C3) =262

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

31.12.1995

01.01.1997

Фомула условного форматирования

=ИЛИ(И(сделки!A2>V!$A$5;сделки!A2<V!$B$5;сделки!C2=V!$C$2);И(сделки!A2>V!$A$5;сделки!A2<V!$B$5;сделки!C2=V!$C$3))

Условнное форматирование может выдать и истину, и ложь

По сути, мы скрепляем между собой условие внутри строки при помощи И, а между строками – при помощи или. Этой формулой задаем условия совпадения значений в таких же!!! столбцах начальной таблицы с этими условиями. То есть каждую ячейку той таблицы мы сраниваем с критериями здесь. Критерии закрепляем долларами (боюсь, что пока допишу пособие, доллар вырастет еще на пару центов;) То есть конкретно: Order date из таблицы больше 31 декабря 1995, меньше 1 января 1997, а Discount из таблицы равен 0, или во второй строчке такие же даты, а равенство со скидкой другое). Ссылаемся не на критерий по датам, а на скопированные отдельно даты без знаков неравенства (см. выше)

Скопировать формулу со знаком равно

Дальше переходим в таблицу, выделяем столбец, который нужно раскрасить, открываем условное форматирование во вкладке главное, добавляем правило. Туда вставляем формулу и выбираем желтый цвет форматирования кнопкой формат. Enter, все должно раскраситься, кол-во ячеек=тому количеству, которое вы искали через БСЧЁТ

Теперь пишем еще одну такую табличку