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, все должно раскраситься, кол-во ячеек=тому количеству, которое вы искали через БСЧЁТ
Теперь пишем еще одну такую табличку
OrderDate | OrderDate | Country |
>31.07.1996 | <01.04.1997 | France |
>31.07.1996 | <01.04.1997 | Germany |
С помощью =ДСРЗНАЧ(october_24;сделки!D1;A7:C9) = 1591находим среднюю цену сделки !!! среди значений, удовлетворяющих критериям.
Запишите где-нибудь рядом отдельное значение >1591
Создать лист, назвать его отчеством, скопируйте туда все заголовки начальной таблицы
Данные->дополнительный фильтр->скопировать результат в другое место
Исходный диапазон – весь лист сделок
Диапазон условий клеточка >1591, который вы записали до этого
Скопировать в диапазон – выделяете все заголовки сверху
Последнюю часть не успеваю
Вот просто формулы для номеров строк и ГПР
=ПОИСКПОЗ(B875:D875;C4:C614;0)
=ГПР($A876;$A$4:$H$614;B$874;ЛОЖЬ)