![]() Главная страница Случайная страница КАТЕГОРИИ: АвтомобилиАстрономияБиологияГеографияДом и садДругие языкиДругоеИнформатикаИсторияКультураЛитератураЛогикаМатематикаМедицинаМеталлургияМеханикаОбразованиеОхрана трудаПедагогикаПолитикаПравоПсихологияРелигияРиторикаСоциологияСпортСтроительствоТехнологияТуризмФизикаФилософияФинансыХимияЧерчениеЭкологияЭкономикаЭлектроника |
Занятие 4 ⇐ ПредыдущаяСтр 3 из 3
СОРТИРОВКА ДАННЫХ. ФИЛЬТРЫ Сортировка – расположение данных таблицы в строго определенной последовательности. Виды сортировок и способы их выполнения: - Простая сортировка. Осуществляется с помощью кнопок - Сложная сортировка. Осуществляется с помощью пункта Сортировка меню Данные. В появившемся диалоговом окне Сортировка диапазона указывается столбец, по которому нужно отсортировать данные, а также порядок сортировки (по возрастанию или по убыванию), при этом в пункте Идентифицировать поля по можно указать, включается в сортировку или нет первая строка диапазона. Здесь же можно задать многокаскадную сортировку (Затем по; В последнюю очередь, по). Это означает, что если по первому сортируемому значению некоторые строки совпадают, то они между собой будут отсортированными по указанному в пункте Затем по столбцу. Третий столбец указывается, если совпадения будут по обоим первым столбцам. Кнопка Параметры… вызывает диалоговое окно Параметры сортировки, которое позволяет определить, что будет подвергнуто сортировке – строки или столбцы. По умолчанию сортируются строки таблицы. Фильтр предназначен для отбора тех строк таблицы, которые удовлетворяют заданному условию, и временно скрывает остальные. Фильтрация данных может выполняться двумя способами: с помощью автофильтра или расширенного фильтра. Фильтрация данных с помощью автофильтра: - выделить диапазон, для которого будет создан фильтр; - в меню Данные выбрать пункт Фильтр – Автофильтр; - раскрыть список столбца, по которому будет осуществляться фильтрация, щелкнув по кнопке - указать требуемые значения или выбрать строку Условие… и задать критерий выборки в диалоговом окне Пользовательский автофильтр. Условия для отбора записей в определенном столбце могут состоять из двух самостоятельных частей, соединенных логической связкой И/ИЛИ. Каждая часть условия может включать: Ø значение, которое может выбираться из списка или содержать шаблонные символы подстановки * и?; Ø оператор сравнения. Способы восстановления всех строк исходной таблицы: - щелкнуть по кнопке - выбрать в меню Данные пункт Фильтр – Отобразить все. Отмена режима автофильтра: выбрать в меню Данные пункт Фильтр – Автофильтр. Фильтрация данных с помощью расширенного фильтра: - сформировать специальную область для задания условий фильтрации данных – диапазон условий отбора (интервал критериев). Диапазон условий должен содержать строку с заголовками столбцов и несколько строк для задания условий отбора. Если условия вводятся в одной строке для разных столбцов, то они считаются связанными логической связкой И. Если критерии отбора записываются в разных строках, то они считаются связанными логической связкой ИЛИ. Между значениями условий отбора и таблицей должна находиться как минимум одна пустая строка; - установить курсор внутри таблицы; - в меню Данные выбрать пункт Фильтр – Расширенный фильтр; - в диалоговом окне Расширенный фильтр указать диапазон ячеек таблицы и диапазон условий отбора. Отмена режима расширенного фильтра: выбрать в меню Данные пункт Фильтр – Отобразить все. Задания: 1. Запустите MS Excel. 2. В файле Склад создайте и заполните следующую таблицу:
3. В последнем столбце произведите вычисления по формуле: Итого=Цена*Количество. 4. Используя простую сортировку, отсортируйте данные таблицы по полю Цена. 5. Отсортируйте наименования организаций в алфавитном порядке, внутри каждой организации наименование товара в алфавитном порядке и внутри каждого наименования товара – по возрастанию количества проданного товара. Для этого: - установите курсор в область таблицы; - выполните команду Данные – Сортировка; - в первом уровне сортировки выберите поле Организация, во втором — Товар, в третьем — Кол-во и нажмите OK. 6. Скопируйте таблицу с Листа 1 на Лист 2 и назовите новый лист Автофильтр. 7. Создайте для первого столбца автофильтр. 8. Выберите из таблицы только те строки, которые относятся к АОЗТ «Белокуриха». 9. Отключите автофильтр. 10. Создайте автофильтр для всех столбцов таблицы. 11. Используя автофильтр, выведите на экран записи, содержащие организацию АОЗТ «Белокуриха», где в столбце «Товар» присутствует «хлеб», т.е. осуществите выборку по двум полям. 12. Восстановите все строки исходной таблицы. 13. Используя автофильтр, выведите на экран записи, содержащие организацию АОЗТ «Белокуриха», в которых цена товара не превышает 16. 14. Восстановите все строки исходной таблицы. 15. Используя автофильтр, выведите на экран записи, содержащие колхоз «Восток» и дату покупки товара в промежутке после 2 марта до 13 июня. 16. Скопируйте таблицу с Листа 1 на Лист 3 и дайте имя листу Расширенный фильтр. 17. Используя расширенный фильтр, выведите на экран записи, содержащие организацию АОЗТ «Белокуриха», где в столбце «Товар» присутствует «Хлеб», т.е. осуществите выборку по двум полям. Для этого: - ниже таблицы, оставив пустые 2 строки, скопируйте строку заголовка таблицы; - в следующей строке сформируйте критерий отбора записей: в столбец Организация введите АОЗТ «Белокуриха», в столбец Товар — «Хлеб»; - установите курсор в область таблицы, из которой будет производиться выборка данных; - выполните команду Данные – Фильтр – Расширенный фильтр…; - в появившемся диалоговом окне в строке Исходный диапазон появится диапазон, соответствующий расположению анализируемой таблицы; - в диалоговом окне установите курсор в строку Диапазон условий, перейдите на рабочий лист и выделите диапазон условий отбора; - для того, чтобы новые данные печатались в другом месте, установите флажок Скопировать результат в другое место и укажите ячейку, начиная с которой будут выводиться найденные с помощью расширенного фильтра данные. 18. Используя расширенный фильтр, выведите на экран записи, содержащие организацию АОЗТ «Белокуриха», в которых цена товара не превышает 16. При этом в диапазоне условий отбора в поле Цена укажите условие: < =16. 19. Сохраните файл Склад и закройте его. 20. Откройте файл Страна.xls. 21. Создайте новую рабочую книгу. 22. Добавьте в новую рабочую книгу еще три рабочих листа и сохраните книгу под именем Занятие 4. 23. Скопируйте таблицу из файла Страна.xls в файл Занятие 4 на Лист1. 24. Закройте файл Страна.xls. 25. Скопируйте Лист1 на все остальные рабочие листы. 26. Расположите на первом рабочем листе страны по алфавиту. 27. На втором рабочем листе с помощью автофильтра выберите страны с площадью более 5000 тыс. км2. 28. На третьем рабочем листе с помощью автофильтра выберите страны с населением меньше 150 млн. чел. 29. На четвертом рабочем листе с помощью автофильтра выберите страны с плотностью населения от 100 до 300 чел./км2. 30. На втором рабочем листе отмените режим фильтрации. 31. На четвертом рабочем листе с помощью расширенного фильтра выберите страны, начинающиеся с буквы «К» и имеющие численность населения более 1 млрд. чел. Чтобы выбрать все страны, названия которых начинаются с буквы «К», нужно при создании условий отбора в поле Страна ввести К*. 32. Сохраните файл и закройте MS Excel. Задание для самостоятельной работы: 1. В файле Телевизоры создайте на первом рабочем листе следующую таблицу и a. используя расширенный фильтр, отобразите на втором рабочем листе все телевизоры Sony с размером экрана 37 и Samsung – с размером экрана 51; b. используя автофильтр, отобразите на первом рабочем листе только те строки, которые относятся к телевизорам, начинающимся на букву S или P.
ЗАНЯТИЕ 5 АНАЛИЗ И ОБОБЩЕНИЕ ДАННЫХ. ПРОМЕЖУТОЧНЫЕ ИТОГИ В СПИСКАХ. КОНСОЛИДАЦИЯ ДАННЫХ. СВОДНЫЕ ТАБЛИЦЫ Microsoft Excel может автоматически обобщать данные, вычисляя промежуточные и общие итоги в списке, т.е. наборе строк в таблице, содержащем связанные данные. Для использования автоматических промежуточных итогов список должен содержать столбцы с подписями и должен быть отсортирован по столбцам, для которых требуются промежуточные итоги. При вставке автоматических промежуточных итогов MS Excel изменяет разметку списка, группируя строки данных с каждой связанной строкой промежуточных итогов, а строки промежуточных итогов — со строкой общих итогов. При подведении промежуточных итогов могут быть автоматически вычислены: сумма, количество значений, среднее, максимум, минимум, произведение и т.д. Значения общих и промежуточных итогов пересчитываются автоматически при каждом изменении данных. Автоматическое подведение итогов осуществляется следующим образом: - отсортировать список по столбцу, для которого необходимо вычислить промежуточные итоги; - выделить какую-либо ячейку таблицы или требуемый диапазон; - выбрать пункт Итоги… меню Данные; - выбрать столбец, содержащий группы, по которым необходимо подвести итоги (столбец, по которому проводилась сортировка), из списка При каждом изменении в; - из списка Операция выбрать функцию, необходимую для подведения итогов; - в списке Добавить итоги по выберите столбцы, содержащие значения, по которым необходимо подвести итоги. При подведении промежуточных итогов создается структура таблицы, пользуясь которой можно скрыть исходные данные и оставить в таблице только результаты подведения итогов. Символы структуры: - Номера уровней. Щелчок по номеру уровня позволяет скрыть детали уровней с большими номерами и показать детали этого уровня и всех уровней с меньшими номерами. Данные первого уровня не могут быть скрыты. Максимальное количество уровней – 8; - Линейки уровней показывают, какие группы строк или столбцов входят в каждый уровень структуры. Щелчок по линейке открытого уровня с детальными данными позволяет скрыть детали этого уровня; - Символ «+» маркирует закрытые уровни. Щелчок по нему позволяет показать детали соответствующего уровня; -
Консолидация данных позволяет объединять данные из нескольких источников и выводить итоги в любой указанной пользователем области. При этом автоматического пересчета данных в итоговой таблице производиться не будет. Процедура консолидации данных: 1. Выделить ячейку, определяющую положение итоговой таблицы. 2. Выбрать пункт Консолидация меню Данные. 3. Указать в окне Консолидация в списке Функция функцию, с помощью которой будет осуществляться подведение итогов. 4. Установить курсор в строку Ссылка. 5. Выделить первую исходную область. 6. Щелкнуть по кнопке Добавить окна Консолидация и нажать клавишу Del. 7. Повторить действия 4-6 для остальных исходных областей. 8. Нажать кнопку OK. Окно Консолидация: Кнопка Обзор … позволяет выбирать исходные области из других рабочих листов и файлов. Группа флажков Использовать в качестве имен предназначена для того, чтобы сообщать программе, каким образом (в строках или столбцах) размещены данные в исходных областях. Флажок Создавать связи с исходными данными устанавливают в случае, когда итоговая область размещена на другом рабочем листе. Если последняя находится на том же листе, что и исходные области, то установка этого флажка вызывает сообщение об ошибке. Сводные таблицы MS Excel – вспомогательные таблицы, с помощью которых можно анализировать большие объемы данных, находящихся в различных источниках, и представлять их в наиболее удобном виде. Сводную таблицу можно создать на основе: - таблиц MS Excel, организованных в виде списка или базы данных; - таблиц, полученных в результате консолидации данных; - других сводных таблиц; - внешних источников данных. Создание и модификация сводных таблиц выполняется с помощью Мастера сводных таблиц и диаграмм. Способы запуска Мастера сводных таблиц и диаграмм: - пункт Сводная таблица… меню Данные; - кнопка Этапы работы с Мастером сводных таблиц и диаграмм: - в первом окне мастера указать, на основе каких данных требуется создать сводную таблицу или диаграмму, и выбрать вид создаваемого отчета (сводная таблица или сводная диаграмма); - во втором окне мастера указать диапазон, в котором содержатся исходные данные; - в третьем окне мастера выбрать место размещения сводной таблицы, с помощью кнопки Параметры… установить параметры вывода информации, с помощью кнопки Макет… сформировать структуру сводной таблицы и нажать кнопку Готово. Окно макета сводной таблицы: В правой части окна находятся кнопки с названиями полей исходных данных, в центре окна – область построения сводной таблицы. Процедура создания макета сводной таблицы: - перетащить поля, в которых нужно произвести отбор нужных записей (фильтрацию), в область Страница. Область Страница может оставаться незаполненной; - поместить поля, которые должны быть представлены в сводной таблице в области Строка и Столбец; В области Страница, Строка и Столбец каждое поле может помещаться только один раз. Для того, чтобы удалить поле из области построения, его кнопку нужно перетащить за пределы области построения. - в область Данные поместить поля, по которым при создании сводной таблицы будут производиться вычисления. Для подведения итогов по одному и тому же полю с помощью нескольких функций это поле должно помещаться в область Данные несколько раз. Для выбора функции и настройки параметров полей, помещенных в область Данные, следует дважды щелкнуть по нужному полю, а затем в диалоговом окне Вычисление поля сводной таблицы выбрать нужную функцию. Вычисляемые поля в сводных таблицах можно создавать и с помощью формул. Для этого нужно: - щелкнуть мышью по сводной таблице; - на Панели инструментов Сводные таблицы щелкнуть по кнопке Сводная таблица и выбрать в раскрывающемся списке команду Формулы – Вычисляемое поле; - щелкнуть по кнопке Добавить, а затем – по кнопке ОК; - расположить вычисляемое поле в требуемом месте отчета. Обновление данных в сводной таблице: - выделить любую ячейку сводной таблицы; - щелкнуть по кнопке При изменении данных в исходной таблице сводная таблица автоматически не обновляется. Ручная правка данных в сводных таблицах невозможна. Удаление сводной таблицы: - установить курсор в любую ячейку сводной таблиц; - на Панели инструментов Сводные таблицы открыть список Сводная таблица и выбрать в нем команду Выделить – Таблица целиком при активизированной кнопке Разрешить выделение; - в меню Правка выбрать пункт Очистить – Все. Задания: 1. Откройте файл Склад.xls. 2. Вставьте в таблицу два новых листа – Лист4 и Лист5. 3. Скопируйте таблицу с Листа1 на Лист4 и Лист5. 4. Перейдите на Лист4. 5. Осуществите автоматическое подведение итогов в таблице по столбцу Итого для каждой организации. Для этого: - отсортируйте данные в столбце Организации таблицы по алфавиту; - выделите какую-нибудь ячейку внутри таблицы; - выберите пункт Итоги… меню Данные; - в раскрывающемся списке При каждом изменении в выберите Организация; - из списка Операция выберите Сумма; - в списке Добавить итоги установите флажок рядом с полем Итоги; - установите флажки Заменить текущие итоги и Итоги под данными; - нажмите кнопку ОК. 6. Используя символы структуры, приведите таблицу к следующему виду: 7. Перейдите на Лист5. 8. Осуществите автоматическое подведение итогов в таблице по столбцу Количество для каждого товара. 9. Сохраните и закройте текущий файл. 10. Создайте новую рабочую книгу и дайте ей имя Хлеб. 11. Создайте в рабочей книге Хлеб следующую таблицу:
12. Осуществите консолидацию данных в созданной таблице для вычисления суммарного объема продаж хлебобулочных изделий. Для этого: - выделите ячейку, определяющую положение итоговой таблицы, например, А22; - выберите пункт Консолидация… меню Данные; - в окне Консолидация в списке Функция укажите функцию Сумма; - установите курсор в строку Ссылка; - выделите первую исходную область A4: D7; - щелкните в окне Консолидация кнопку Добавить и нажмите клавишу Del; - повторите предыдущие три действия для диапазонов A9: D13, A15: D18; - установите флажок Использовать в качестве имен значения левого столбца и нажмите ОК. 13. Осуществите консолидацию данных исходной таблицы для вычисления минимального объема продаж хлебобулочных изделий. 14. Сохраните и закройте текущий документ. 15. Создайте в файле Сводная таблица следующую таблицу:
16. На основании имеющихся данных постройте сводную таблицу. Для этого: - вызовите Мастер сводных таблиц; - на первом шаге мастера установите флажки Создать таблицу на основе данных, находящихся в списке или базе данных Microsoft Excel и Вид создаваемого отчета сводная таблица; - на втором шаге мастера укажите диапазон $A$1: $E$11; - на третьем шаге установите флажок Поместить таблицу в новый лист, создайте макет будущей таблицы (кнопка Макет…): поместите мышью кнопку Точка в область Страница, кнопку Наименование – в область Строка, кнопку Цена – в область Столбец, кнопку Сумма – в область Данные, нажмите ОК, а затем кнопку Готово. В результате на Листе 4 будет таблица вида: 17. Используя фильтры сводной таблицы (кнопки 18.
19. Сохраните текущий документ и закройте MS Excel. Задания для самостоятельной работы: 1. Для данной таблицы осуществите автоматическое подведение итогов по столбцу Количество для каждого наименования.
2. Для следующей таблицы заполните поле Объем продаж и подведите итоги для каждой программы по количеству и объему продаж.
|