 |
Структурированные данные. Промежуточные и итоговые результаты.
|
|
|
|
B110200 «Общественное здравоохранение»
B110400 «Медико-профилактическое дело»
B130100 «Общая медицина»
B130200 «Стоматология»
Предмет Информатика
Курс 1
ИНФОРМАЦИОННО –ДИДАКТИЧЕСКИЙ БЛОК
И МЕТОДИЧЕСКИЕ УКАЗАНИЯ
ДЛЯ СТУДЕНТОВ
К ПРАКТИЧЕСКМУ ЗАНЯТИЮ
ТЕМА 18:
«Средства обработки информации. MS Excel как база данных. Промежуточные итоги. Консолидация.
Сводная таблица и диаграмма.»
Составитель
старший преподаватель Дихамбеков Ж.К.
Семей, 2011 г.
Информационно-дидактический блок и методические указания для студентов к практическому занятию на тему «Средства обработки информации. MS Excel как база данных. Промежуточные итоги.Консолидация. Сводная таблица и диаграмма.»
обсуждена на заседании кафедры (курса) от 2011 года
Протокол №
Ответственный по курсу Буланова Р.К.
1. Тема: «Средства обработки информации. MS Excel как база данных. Промежуточные итоги.Консолидация. Сводная таблица и диаграмма.»
2. Количество учебных часов:: 3 академических часа (150 мин)
3. Актуальность темы (мотивация): Освоение работы электронных таблиц как база данных.
4. Цель занятия:
Ознакомить и научить студентов оформлению информации в виде таблицы и обработка информации в таблице.
5. Задачи обучения: Привитие навыков работы с базами данных, создания итогов и сводной таблицы
6. Студент должен знать:
1. Запуск документов Microsoft Excel.
2. Основные элементы окна табличного процессора (приложения) и его документа.
3. Выделение ячейки и блока ячеек.
7. Студент должен уметь:
- пользоваться меню,
- пользоваться диалоговыми окнами,
- производить одно и много уровневые сортировки,
|
|
|
- составлять промежуточные и итоговые таблицы,
- составление сводной таблицы,
- консолидация таблиц.
8. Попросы самопроверки:
а) по базисным знаниям:
1. Запуск документов Microsoft Excel.
2. Основные элементы окна табличного процессора (приложения) и его документа.
3. Выделение ячейки и блока ячеек.
4. Диалоговые окна, как указывать и вводить в них информацию.
Б) по теме занятия
1. Составление базы данных
2. Сортировка одноуровневая и много уровневая. Выполнение сортировки данных.
3. Фильтр. Усатановка фильтра. Фильтрирование по определенным данным и диапазона данных с помощью пользовательского фильтра. Снятие фильтра.
4. Структруирование данных. Промежуточные и общие итоги. Составление таблицы с промежуточными и общими итогами.
5. Консолидация. Составление таблицы консолидации.
6. Отчет и диаграмма сводной таблицы. Составление сводного отчета и сводной таблицы.
9. Әдебиеттер
Негiзгi әдебиеттер:
1. Адибаев Б.М. және басқалар, Информатика, Алматы, 1997, бет, 80-89.
2. Камардинов О., Информатика, Алматы, “Ғылым” БО, 2004, 205 – 211.
3. Изтлеуов М.К. және басқалар, Ақтөбе, 2005, 260 – 274.
Қосымша әдебиеттер:
1. Ефимова О., Морозов В., Шафрин Ю. Курс компьютерной технологии, 1-том, Москва, АБФ, 1998, бет, 452-477.
2. Ефимова О., Морозов В., Шафрин Ю. Курс компьютерной технологии, 2-том, Москва, АБФ, 1998, бет. 268-278.
3. Микляев А.П., Настольная книга пользователя, Москва, бет, 329-366.
4 Пасько В., Колесников А., Самоучитель работы не персональном компьютере, Киев, 2000, бет, 309-328.
ИНФОРМАЦИОННО-ДИДАКТИЧЕСКИЙ БЛОК (АННОТАЦИЯ, ПОСОБИЯ)
Табличный процесор Microsoft Excel используется не только для производства вычислении по табличным данным, но вместе с тем, как база данных, способствующая сортировке данных или выбора данных по определенным условиям.
При использовании Microsoft Excelді как базу данных данные заполняются в таблицу, при чем каждый столбец должен иметь заголовок и в строке заголовка не должно быть объединенных ячеек. Если для полной ясности необходимо присутствие в таблице объединенных ячеек, то для заголовка столбцов нужно отвести несколько строк, но на самой нижней строке не долно быть объединенных ячеек и каждголовок (рис. 1).
|
|
|
Сортировка.
Одно из видов работы с базой данных - это представление данных в определенном порядке, т.е. сортировка данных. Сортировку данных можно проводить по одному столбцу или по нескольким столбцам базы данных. Сортировка может быть произведена в двух направлениях: по возрастанию от А до Я или по убыванию от Я до А.
Для выполнения сортировки по одному столбцу базы данных нужно выделить несколько данных этого столбца и выполнить одно из этих двух действий:
1) либо «Главная» – «Сортировка и фильтр» группы «Редактирование» - «Сортировка от А до Я» или «Сортировка от Я до А»
2) либо «Данные» - 
или 
группы «Сортировка и фильтр».
Во время сортировки строка полностью перемещается с сортируемым данным столбца.
Если сортировка производится по нескольким столбцам базы данных, то
1) нужно выделить несколько данных таблицы;
2) вызвать диалоговое окно «Сортировка» (рис. 2), выполнив
А) либо «Главная» - «Сортировка и фильтр» группы «Редактирование» - «Настраиваемая сортировка...»
Б) либо «Данные» - «Сортировка и фильтр» группы «Сортировка»;
3) в диалоговом окне «Сортировка» необходимо установить условия первого уровня сортировки, для чего выполнить:
А) щелкнуть по стрелке поля «Сортировать по» группы «Столбец» и в появившемся списке названии столбцов выбрать нужное;
Б) щелкнуть по стрелке поля «Значения» группы «Сортировка» и в появившемся списке типов данных: значения, цвет ячейки, цвет шрифта, значок ячейки – выбрать по какому из них будет произведена сортировка;
В) щелкнуть по стрелке поля «Порядок» и указать направление сортировки «от А до Я» или «от Я до А», или «настраиваемый список...»;
4) щелкнуть по кнопке «Добавить уровень» и выполнить все действия по установлению условий сортировки пункта 3 для появившейся строки;
5) повторением 4-го пункта установить условия сортировки для третьего, четвертого, итд уровней;
|
|
|
6) после установления условий сортировки для всех уровней подтвердить сортировку нажатием кнопки «OK» диалогового окна «Сортировка».
Фильтрация.
Если список данных в таблице базы данных очень большое, то сортировка не всегда может быт удачным действием. В таких случаях нужно воспользоваться фильтрованием, который создает возможность выбора нужных данных из свей совокупности.
C помощью фильтрации данных на листе можно быстро находить нужные значения. Выполнять фильтрацию можно по одному или нескольким столбцам данных. С помощью фильтра можно контролировать не только отображаемые, но и исключаемые данные. Можно выполнять фильтрацию на основе выбранных в списке параметров или создавать специальные фильтры, чтобы сконцентрироваться на необходимых данных.
При фильтрации с помощью поля «Поиск «в интерфейсе фильтра можно выполнять поиск текста и чисел.
Если при фильтрации данных значения в одном или нескольких столбцах не удовлетворяют условиям фильтрации, строки будут скрыты целиком. Выполнять фильтрацию можно по числовым или текстовым значениям или по цвету ячеек, к тексту или фону которых применено цветное форматирование.
Для фильтрации нужно выполнить следующие действия:
1) Выделить полностью или частично (без названия солбцов)данные электронной таблицы;
2) Для установления значка 
фильтра в ячейках названий столбцов (рис. 3) нужно выполнить:
А) либо «Главная» - «Сортировка и фильтр» из группы «Редактирование» - «Фильтр»;
Б) либо «Данные» - «Фильтр»,группы «Сортировка и фильтр»;
3) Щелкнув по значку фильтра в столбце, по которой проводится сортировка, можно получить список занчений, которые могут быть использованы для условии фильтрации (рис. 4). Примечание: В зависимости от типа данных появляется команда либо «Числовые фильтры» либо «Текстовые фильтры» (рис. 4). Команды, входящие в список их подчиненных меню, отличаютяс друг от друга (рис. 5).
При выборе любого элемента «Числового фильтра» или «Текстового фильтра» (рис. 5) появляется, диалоговое окно «Пользовательский автофильтр» (рис. 6). С помощью этого окна фильтрация производится в определенном диапазоне данных (рис. 6).
|
|
|
4) Для выбора условии фильтрации необходимо выполнить: а) убрать флажок перед «Выделить всё», б) поставить флажок перед данными по которым будет производиться фильтрование, щелкнуть по кнопке «ОК» (рис. 4). Все строки в которых имеются данные, соответствующие условиям фильтрации для данного столбца, будут выведены для просмотра, а остальные будут скрыты. Например, на рисунке 4 для столбца фильтрации установлены условия из-за которых на экран выводятся строки с числами 8, 10 и 13.
Для показа всех данных после завершения фильтрации нужно щелкнуть по значку фильтра столбца по которой производилась фильтрация и щелкнуть по команде «Выделить все» из появивщегося списка.
Убрать фильтр можно выполнив одно из следующих действии:
А) либо «Главная» - «Сортировка и фильтр» из группы «Редактирование» - «Фильтр»;
Б) либо «Данные» - «Фильтр»,группы «Сортировка и фильтр».
Структурированные данные. Промежуточные и итоговые результаты.
Для группировки и суммирования данных из списка можно создать структуру (рис. 7),, включающую до восьми уровней (рис. 7, 1)., по одному для каждой группы. Каждый внутренний уровень, представленный большим числом в символах структуры документа (Символы структуры. Символы, используемые для изменения вида структурированного листа. Подробные данные можно показать или скрыть, щелкнув знак «плюс», «минус» или цифры 1, 2, 3, 4, показывающие уровень структуры.)(2,3 рис. 7. 1), отображает подробные данные (Подробные данные. В таблицах с итогами и структурах — строки или столбцы с исходными данными, по которым вычисляются итоги. Подробные данные обычно расположены непосредственно сверху или слева от итогов.) для предшествующего внешнего уровня. При этом внешний уровень представляется меньшим числом в символах структуры документа. Используйте многоуровневую структуру для быстрого отображения итоговых строк или столбцов или вывода подробных данных для каждой группы. Щелкнув по значкам «плюс», «минус» или по цифрам 1, 2, 3, 4,..., указывающим уровни структуры можно показать или скрыть подробные данные. Подробные данные это строки или столбцы, содержащие исходные (начальные) данные. На основании подробных данных расчитываются итоги, а в таблицах со структурой итоги для каждого уровня структуры. Если структурирование производится по строкам, то подробные данные располагаются выше итогов, если структурирование производится по столбцам, то подробные данные располагаются правее итогов. Для каждой группы для быстрого показа строки или столбца или подробных данных (показано в нижеприведенном примере) можно составить много уровневую структуру по строкам, или по столбцам, или по строкам и столбцам.
|
|
|
Например, на рисунке 7:
1- Для отображения строк уровня щелкнуть соответствующие 
символы структуры.
2- Уровень 1 содержит сумму продаж по всем строкам с подробными данными.
3- Уровень 2 содержит сумму продаж по каждому месяцу в каждом регионе.
4- Уровень 3 содержит строки с подробными данными (отображаются только строки данных с 11 по 13).
5- Для отображения или скрытия данных в многоуровневой структуре щелкайте символы структуры 
и 
.
С помощью команды «Промежуточные итоги» можно автоматически подсчитать промежуточные и общие итоги в списке для столбца. Промежуточные итоги вычисляются с помощью «Итоговой функции». Итоговая функция это тип вычисления при объединении данных в отчете сводной таблицы, в таблице консолидации или при вычислении итогов в списках или базах данных. Примерами итоговых функций могут служит функции вычисления суммы, среднего значения, максимума, минимума, призведения, количества значений, отклонения, смещения отклонений, дисперсию, дисперсию отклонений. Для каждого столбца можно применить несколько типов функции.
Если для книги выбран режим автоматического вычисления по формулам, то с помощью команды «Промежуточные итоги» можно автоматически пересчитывать значения промежуточных и общих итогов при каждом изменении подробных данных. Используя команду «Промежуточные итоги», можно также отобразить (Структура. Режим электронной таблицы, при которой строки или столбцы сгруппированы так, что можно видеть итоги. В структуре возможно подведение итогов как по всему листу, так и по его выбранному диапазону.) список, что позволит отображать и скрывать строки каждого из промежуточных итогов.
Общие итоги вычисляются не на основе подробных данных, а на основа промежуточных итогов.
Для вставки одного уровня промежуточного итога и общего итога нужно выполнить:
1) Убедится в том, что первая строка каждого столбца диапазона (Диапазон. Две или более ячеек листа. Ячейки диапазона могут быть как смежными, так и несмежными.) данных, для которых рассчитываются промежуточные итоги, содержит название, а сами столбцы — однотипные данные, и что пустые строки или пустые столбцы в диапазоне отсутствуют.
2) Выделить ячейку в диапазоне.
3) Провести сортровку, если возможно многоуровневую, данных таблицы (базы данных) выполнив: «Данные» - «Сортировка» группы «Сортировка и фильтр». Например, на рисунке 7 первый уровень сортировки проведена по «Регион» по возрастанию, второй уровень - по «Месяц» по возрастанию, третьий уровень - по «Продажи» по возрастанию.
4) 
Выполнив «Данные» - «Промежуточные итоги» группы «Структура», открыть диалоговое окно «Промежуточные итоги».
5) В поле «При каждом изменении в:» (рис. 8). выбирается столбец по которому будет вычисляться итог. Для примера на рисунке 7 выбран столбец «Месяц».
6) «Операция» выбирается итоговая, которая вычисляет промежуточный итог. Для примера на рисунке 7 выбрана функция «Сумма».
7) В поле «Добавить итоги по:» устанавливается флажок перед наименованиями полей, которые содержат данные, необходимые для вычисления итога. Для примера на рисунке 7 флажок поставлен перед «Продажи».
8) Чтобы за каждым итогом следовал автоматический разрыв страницы, устанавливается флажок «Конец страницы между группами».
9) Чтобы расположить итоговую строку над строкой данных, снимите флажок Итоги под данными. Чтобы расположить итоговую строку под строкой данных, установите флажок Итоги под данными. Для примера на рисунке флажок поставлен.
10) Для того чтобы не повторялись установленные итоги необходимо снять флажок перед «Заменить текущие итоги».
11) При необходимости команду Промежуточные итоги можно использовать снова, повторив шаги 1 - 7, чтобы добавить дополнительные строки итогов с использованием других функций.
Вставка внешнего уровня промежуточного итога выполняется при:
Повторении установки промежуточного итога и общега итога с 4-го пункта.
В 5-ом пункте в поле «При каждом изменении в» включить другой столбец для вычисления итога. Для примера на рисунке 7 выбран столбец Регион.
Остальные действия остаются без изменения.
Консолидация.
Чтобы подвести итоги и составить отчет по результатам нескольких листов, можно консолидировать данные из отдельных листов на основном листе. Листы могут находиться в той же книге, что и основной лист, или в других книгах. При консолидации данных они компонуются так, что их становится проще обновлять и обобщать на регулярной основе или при необходимости.
Например, если имеется лист расходов для каждого регионального представительства, консолидацию можно использовать для преобразования этих данных в корпоративный лист по расходам. Этот основной лист может содержать общие и средние объемы продаж, текущее количество товаров на складах и сведения о продуктах, пользующихся наибольшим спросом, по всей организации.
Существует два основных способа консолидации данных.
Консолидация по расположению Этот метод используется, если данные из различных источников упорядочены одинаково и в них используются одни и те же подписи столбцов и строк (например, при наличии нескольких листов расходов, созданных из одного и того же шаблона).
Консолидация по категории Этот метод используется, если данные из различных источников упорядочены по-разному, но и в них используются одни и те же подписи столбцов и строк (например, при наличии листов с данными о запасах для каждого месяца, которые имеют одинаковый макет, но содержат разные элементы или разное число элементов).
Чтобы консолидировать данные, нужно воспользоваться командой Консолидация (вкладка Данные, группа Работа с данными). Для консолидации можно также использовать формулу или отчет сводной таблицы.
Консолидациялау жүргізу үшін орындалады:
1) На каждом листе, который требуется консолидировать, подготовить данные, как описано ниже.
§ Необходимо, чтобы все диапазоны данных были представлены в формате списка: первая строка каждого столбца содержит подпись, остальные строки — однотипные данные; пустые строки или столбцы в списке отсутствуют.
§ Разместить каждый диапазон на отдельном листе. Не помещать диапазоны на лист, на котором должна выполняться консолидация.
§ Убедиться, что макеты всех диапазонов совпадают. Если часто требуется консолидировать данные, рекомендуется создавать листы на основе шаблона с единообразным макетом.
2) На основном листе щелкнуть левый верхний угол области, в которой требуется разместить консолидированные данные.
3) Запустить команду «Консолидация» на вкладке «Данные» в группе «Средства обработки данных». Откроется диалоговое окно «Консолидация» (рис. 9).
4) Щелкнуть по стрелке в поле «Функция» и раскрывшемся списке выбрать итоговую функцию.
5) Если лист с данными для консолидации находится в той же книге, выполнить указанные ниже действия:
А) В поле «Ссылка» нажать кнопку закрытия диалогового окна, чтобы выбрать данные на листе;
Б) Щелкнуть по ярлыку листа с данными, которые необходимо консолидировать, выделить нужный диапазон данных таблицы и нажать кнопку раскрытия диалогового окна;
В) В диалоговом окне «Консолидация» нажать кнопку «Добавить» и повторить действия А и Б, чтобы добавить все нужные диапазоны;
Г) Выбрать способ обновления консолидации. Для этого нужно выполнить одно из указанных ниже действий.
· Чтобы консолидация обновлялась автоматически при изменении исходных данных в другой книге, установить флажок «Создавать связи с исходными данными»;
· Установить флажки в группе «Использовать в качестве имен», указывающие, где в исходных диапазонах находятся названия: либо «Подписи верхней строки», либо «Значения левого столбца», либо оба флажка одновременно.
5. Сводная таблица и сводная диаграмма.
Можно создавать сводную таблицу и сводную диаграмму по диапазонам предназначенным для консолидации. Отчет сводной таблицы представляет собой интерактивный способ быстрого обобщения больших объемов данных. Этот метод подобен консолидации по категориям с способствет гибкому регулированию категорий.
Отчет сводной таблицы очень полезен при вычислении суммы больших совокупностей данных.
Для создания сводной таблицы и сводной диаграммы нужно выполнить:
1. Щелкнуть по любой ячейке таблицы;
2. Щелкнуть по пиктограмме «Сводная таблица» гшруппы «Таблицы» вкладки «Вставка»;
3. на диалоговом окне «Создание сводной таблицы» (рис 10) выбрать вариант «Выбрать таблицу или диапазон» и если указанный диапазон не удовлетворяет, то щелкнуть по значку в поле «Таблица или диапазон» и выбрать из таблицы нужный диапазон, который будет указан в данном поле;
4. Для того чтобы указать расположение, создаваемой сводной таблицы в диалоговом окне «Создание сводной таблицы» нужно выполнить:
А) в целях создания нового листа и расположения в нем сводной таблицы нужно щелкнуть по «На новый лист»;
Б) в целях расположения таблицы в существующем в книге листе нужно установить значок точки перед крмандой «На существующий лист», затем щелкнуть по полю «Диапазон:» и указать левый верхний угол области где будет располагаться сводная таблица;
5. Щелкнуть по кнопке «ОК» диалогового окна;
6. В диалоговом окне «Список полей сводной таблицы» (сурет 11) выполнить:
А) в поле «Выберите поля для добавления в отчет» щелкнуть в нужной последовательности наименования полей и перевести в поле «Названия строк»;
Б) в поле «Выберите поля для добавления в отчет» щелкнуть в нужной последовательности наименования полей и перевести в поле «∑ Значения»;
7. Закрыть диалоговое окно «Список полей сводной таблицы»;
8. Щелкнув по команде «Сводная диаграмма» группы «Сервис» вкладыша «Работа со сводными таблицами Параметры» можно получить сводную таблицу;
9. В таблице и диаграмме можно показать только нужные элементы щелкнув по значкам «+» или «-» сводной таблицы либо по значку фильтра в первой строке сводной таблицы или нижней части сводной диаграммы и включить (вставить флажок) или скрыть (убрать флажок).
СОДЕРЖАНИЕ ЗАНЯТИЯ.
САМОСТОЯТЕЛЬНАЯ РАБОТА.
1. Получить задание у преподавателя.
2. Открыть документ и лист, указанный в задании.
3. Ввести заголовки задания и столбцов базы данных как указано в задании.
4. Ввести данные в таблицу. При необходимости форматировать ширину столбцов.
5. Сортировать данные по заданию.
6. Выписать результаты сортировки в тетрадь.
7. Произвести фильтровку данных таблицы по заданию.
8. Выписать результаты фильтровки в тетрадь.
11.2. РАБОТА С ПРЕПОДАВАТЕЛЕМ:
- При появлении затруднений или при ошибочных действиях обратиться к преподавателю для исправления ошибок.
- К концу занятия показать преподавателю отчет о выполненной работе и получить зачет по данной работе.
11.3. КОНТРОЛЬ ИСХОДНОГО И ЗАКЛЮЧИТЕЛЬНОГО УРОВНЯ ЗНАНИЙ:
- Тестирование на компьютере.
- Беседа преподавателя со студентом по вопросам самоподготовки.
|
|
|
