Фильтрование. Расширенный фильтр
Задание 2
Определить книги «Автора1» - Б. Пастернак.
Технология выполнения:
1. Создать копию листа «Исходные данные» и переименовать в «2».
2. На вкладке «Данные» выбрать команду «Фильтр».
Рис. 6. Меню Данные, вкладка Фильтр
3. Под заголовком «Автор» щёлкнуть на стрелочку и снять выделение со всех авторов, кроме «Автора1» - Б. Пастернака.
Рис. 6. Настройки фильтра
4. Нажать Ок.
Задание 3
Определить книги «Автора1» - Б. Пастернак и «Автора2» - Р. Брэдбери.
Технология выполнения:
1. Выполнить аналогично заданию 2, оставив выделение уже у двух авторов.
Рис 8. Настройки фильтра
Задание 4
Определить книги, автор которых «Автор1» - Б. Пастернак и место издания «Место издания1» - Москва.
Технология выполнения:
1. Создать копию листа «Исходные данные» и переименовать в «4».
2. На вкладке «Данные» выбрать команду «Фильтр».
Рис. 9. Меню Данные, вкладка Фильтр
3. Под заголовком «Автор» щёлкнуть на стрелочку и снять выделение со всех авторов, кроме «Автора1» - Б. Пастернака.
4. Нажать Ок.
5. Под заголовком «Место издания» щёлкнуть на стрелочку и снять выделение со всех мест, кроме «Места издания1» - Москва.
6. Нажать Ок.
Задание 5
Определить книги, автор которых «Автор1» - Б. Пастернак или место издания «Место издания1» - Москва.
Технология выполнения:
Создать копию листа «Исходные данные» и переименовать в «5».
Скопировать и вставить три пустых строчки между заголовками и результатами значений.
|
|
|
Написать в первой пустой строчке под «Автором» =«=Б. Пастернак»
Написать во второй пустой строчке под «Местом» =«=Москва»
5. На вкладке «Данные» выбрать команду «Дополнительно» рядом с кнопкой «Фильтр»
Рис. 10. Меню Данные, вкладка Фильтр
1. В исходный диапазон указать всю таблицу полностью - '5'!$A$1:$I$34
2. В диапазон условий – Строчки заголовков и условий, в данном случае это $A$1:$I$3
Рис. 11. Диалоговое окно «Расширенный фильтр»
3. Нажать Ок.
Задание 6
Определить книги, цена которых находится в некоторых пределах. Отсортировать эти данные сначала по году издания, затем по цене.
Технология выполнения:
1. Создать копию листа «Исходные данные» и переименовать в «6.1».
2. На вкладке «Данные» выбрать команду «Фильтр».
3. Щёлкнуть по стрелочке под заголовком «Цена». Выбрать «Числовые фильтры» - «между..»
4. В верхнем пустом поле появившегося окна ввести значение min.
5. В нижнем пустом поле - значение max.
Рис. 12. Диалоговое окно «Пользовательскийавтофильтр»
6. Нажать Ок.
7. Создать копию листа «6.1» и переименовать в «6.2».
8. Отсортировать по году издания, как это делали в задании 1.
9. Создать копию листа «6.2» и переименовать в «6.3».
|
|
|
10. Отсортировать по цене, как это делали в задании 1.
Рис. 13. Выпадающее меню кнопки «Сортировка и фильтр»
Задание 7
Определить авторовкниг, место издания книг которых «Место издания2» - Екатеринбург и тираж находится в пределах от xxx до yyy. Отсортировать полученные данные сначала по тиражу, а затем – по цене книг.
Технология выполнения:
1. Создать копию листа «Исходные данные» и переименовать в «7».
2. Отфильтровать список по месту издания «Место издания2» - Екатеринбург.
3. Отфильтровать Тираж, как это делали в задании 6.
4. Отсортировать Тираж, как это делали в задании 1.
5. Отсортировать Цену, как это делали в задании 1.
Фильтрование. Сводная таблица
Задание 9
Определитькниги, количество страниц которых меньше заданного числа, а тираж находится в некоторых пределах. Отсортировать эти данные сначала по теме, затем – по цене книг.
Технология выполнения:
1. На исходном листе выбрать весь диапазон данных.
2. На вкладке «Вставка» выбрать команду «Сводная таблица…». В появившемся окне нажатьОк.
3. Появится новый лист с макетом сводной таблицы. Переименовать его в «9».
Рис. 14. Диалоговое окно «Создание сводной таблицы»
4. Методом перетаскивания заполнить макет сводной таблицы, используя таблицу «Список полей сводной таблицы»:
|
|
|
Рис. 15. Макет сводной таблицы и Список полей сводной таблицы
· Поле строк: Год издания;
· Поле столбцов: Тема книги;
· Поле страниц: Тираж.
5.Щелкнуть правой клавишей по полю значений и выбрать «параметры поля значений»
6. Выбрать операцию «Среднее» и нажать ОК.
7. Отключить общие итоги в Параметрах сводной страницы.
Рис. 16. Параметры сводной таблицы.
8. В строке, следующей за сводной таблицей, в ячейке под значениями тиража Классической литературы ввести формулу суммы значений этого столбца. =СУММ(B5:B17).
9. Перетащить формулу на остальные столбцы сводной таблицы.
Задание 10
Определить среднюю цену книг данного автора с учетом суммарного тиража за конкретный год издания.
Технология выполнения:
1. Создать сводную таблицу на новом листе со следующими данными:
· Поле строк: Год издания;
· Поле столбцов: Автор книги;
· Поле страниц: Сумма по тиражу и Сумма по цене.
2. В параметрах сводной таблицы оставить общий итог для строк.
3. В соседнем столбце от сводной таблицы посчитать произведение суммы тиража на сумму цены в каждой соответствующей строчке.
4. Сложить получившиеся значения в следующей, после вычислений ячейке.
|
|
|
5. Поделить Сумму значений на общий итог.
Задание 11
Определить ее количество страниц для данного издательства за конкретный год издания.
Технология выполнения:
1. Создать сводную таблицу на новом листе со следующими данными:
· Поле строк: Издательство;
· Поле столбцов: Год издания;
· Поле страниц: Среднее по полю Количество страниц.
2. Убрать общие итоги.
Задание 12
Определить общее количество страниц для данного автора с учетом средней цены для конкретной темы.
Технология выполнения:
1. Создать сводную таблицу на новом листе со следующими данными:
· Поле строк: Тема произведения;
· Поле столбцов Автор;
· Поле страниц: : Сумма по полю количество страниц и Среднее по полю цена.
Задание 13
Создать сводную таблицу, выводящую для данной темы книг общее количество страниц и средний тираж. Вычисляемое поле переводит цену книги из российских рублей в у.е. (или наоборот).
Технология выполнения:
1. Создать сводную таблицу на новом листе со следующими данными:
· Поле строк: Тема произведения;
· Поле столбцов:Значения;
· Поле страниц:Сумма по полю количество страниц, Среднее по полю тираж
2. Добавить вычисляемое поле с формулой: =цена/30. (перевод рублей в у.е.)
Рис. 16. Диалоговое окно «Вставка вычисляемого поля»
Макрос
Задание V
Создать макрос, с помощью которого в поле «Тема» определяются неповторяющиеся значения; эти значения выводятся в ячейке перечислением через запятую.
Технология выполнения:
1. Скопировать лист «Исходные данные» и переименовать в «Макрос».
2. На вкладке «Разработчик» выбрать «Запись макроса»:
Рис. 17. Вкладка Разработчик, команда Запись макроса
3. Выделить столбец «Тема книги». Скопировать.
4. Через специальную вставку вставить в ячейку Н33.
5. Применить к скопированному диапазону операцию «Сортировка по убыванию».
6. В ячейку I34 ввести формулу: «=Н34»; в ячейку I35 с помощью мастера функций ввести формулу: «=ЕСЛИ(H34=H35;I34;I34&", "&H35)».
7. Полученную формулу скопировать с помощью маркера автозаполнения.
8. Получим требуемый результат в последней ячейке диапазона.
9. Скопировать содержимое этой ячейки и вставить через «Специальную вставку», задав параметр: вставить - значения.
10. Остановить макрос. Создан нужный макрос.
Дата добавления: 2018-05-12; просмотров: 180; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!