Изменение источника данных

Тема №5. Кодирование и классификация в системе здравоохранения

Цель: Ознакомиться с интерфейсом Excel, получить навыки работы с программой Excel. Научиться вводить формулы, строить диаграммы, проводить элементарную статистическую обработку данных.

Задачи обучения: Формирование умений и навыков работы с блоками, ввода формул, построения диаграммы, проведения элементарных статистических обработок данных с помощью табличного процессора MS Excel.

Основные вопросы темы:

Пользовательский интерфейс.
Работа с блоками.
Работа с Мастером функций.
Работа с Мастером диаграмм.
Проведение статистической обработки данных: определение среднего значения, дисперсии.
Построение полигонов и гистограмм.
Математическая модель корреляционной зависимости и построение графика.

Методы обучения и преподавания: работа с программой MS Excel

Теоретический блок

Интерфейс Excel 2007 построен по аналогии с интерфейсом Word 2007 и кардинально отличается от предыдущих классических версий 1997-2003.

Все, что создавалось в рабочей среде Word 2007, называлось документами. В силу специфики создаваемых в Excel 2007 «документов» (по сути это электронные таблицы) они получили название рабочая книга. Каждая рабочая книга состоит из нескольких рабочих листов, названия которых отображаются в нижней части рабочего экрана (по умолчанию Лист1, Лист2 и т.д.). Если требуется создать новый рабочий лист, то щелкните мышью по пиктограмме Вставить лист, которая находится справа от названия последнего рабочего листа.

Каждый рабочий лист представляет собой сетку, состоящую из столбцов и строк. В месте пересечения строки и столбца образуются ячейки, для каждой из которых определяется уникальный адрес, который называется ссылкой. Так, например, в месте пересечения столбца с А и строки с номером 3 образуется ячейка, адрес которой определен в виде А3. Адреса используются при записи формул или обращении к ячейкам.

Стартовое окно программы содержит три пустых листа рабочей книги.

Вверху находятся семь лент с инструментами: Главная, Вставка, Разметка страницы, Формулы, Данные, Рецензирование, Вид.

Каждая лента состоит из панелей, на которых, собственно, и расположены инструменты для работы с электронными таблицами. Как правило, на панель вынесены наиболее часто используемые инструменты. Для вызова полного набора инструментов той или иной панели надо открыть окно данной панели, нажав на стрелочку в правом нижнем углу.В левом верхнем углу окна программы находится главная кнопка программы "Office".

По умолчанию программа сохраняет файл с расширением xlsx, который не смогут прочитать предыдущие версии Excel. Для того чтобы документ был совместим с ранними версиями электронных таблиц, необходимо во время сохранения файла выбрать соответствующую опцию.

Лента — это новый центр управления.

Вместо трех десятков скрытых панелей инструментов и команд, находящихся внутри меню, теперь появился единый центр управления — лента. На ней объединены и визуализированы все важные инструменты.

Лента состоит из объектов трех типов: вкладок, групп и команд.

Лента содержит три основных типа объектов:

Вкладки. Вкладок всего семь, и они находятся в верхней части окна. Каждая из них предоставляет возможность выполнять основные задачи, предусмотренные в Excel.

Группы. Каждая вкладка состоит из групп, объединяющих связанные друг с другом элементы.

Команды. Команда — это кнопка, поле для ввода данных или меню.

Основные команды Excel собраны на первой вкладке — Главная. По мнению, корпорации Майкрософт, эти команды являются наиболее часто используемыми при выполнении основных задач с листами.

Например, команды Вставить, Вырезать и Копировать расположены первыми на вкладке Главная в группе Буфер обмена. Далее в группе Шрифт находятся команды форматирования шрифта. Команды для центрирования или выравнивания текста по левому или правому краю расположены в группе Выравнивание, а команды для вставки и удаления ячеек, строк, столбцов и листов находятся в группе Ячейки.

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

Приведем пример удобства работы с ними. Если в ячейке нужно отобразить текст в несколько строк, не нужно выбирать команду меню, вкладку диалогового окна, а затем — параметр в диалоговом окне. Вместо этого можно просто нажать кнопку Переносить по словам в группе Выравнивание на вкладке Главная.

При этом откроется соответствующее окно с инструментами.

Проверка вводимых данных

Очень часто при вводе данных в ячейки электронной таблицы совершаются ошибки.

Если четко знать каким условиям должны удовлетворять вводимые данные, то ошибок можно избежать. Для этого надо задать условия проверки вводимых значений. Чтобы не допустить вышеуказанные ошибки, необходимо выделить диапазон ячеек, в которые будут вводиться данные. Затем выбрать инструмент "Проверка данных" на панели "Работа с данными" ленты "Данные". Из выпадающего списка выберите значение "Проверка данных".

В появившемся окне "Проверка вводимых значений" на вкладке "Параметры" задайте условия проверки.

На вкладке "Сообщение для ввода" введите текстовые значения, которые будут показаны пользователю, когда ячейка выбрана.

На вкладке "Сообщение об ошибке" введите текстовые значения, которые будут показаны пользователю, когда в ячейку введено ошибочное значение.

Вот как это будет выглядеть в процессе работы.

Если в окне "Проверка вводимых значений" на вкладке "Параметры" установить в поле "Тип данных" значение "Список", то можно упростить ввод повторяющихся данных.

Для этого надо предварительно ввести все допустимые значения из списка. Затем в поле "Источник" указать диапазон ячеек, где расположены допустимые списочные значения:

нажмите изображение таблицы, расположенной справа поля;
выделите диапазон ячеек;
повторно нажмите на изображение таблицы.

После этого, при выборе ячейки будет появляться стрелочка, нажав на которую можно выбрать нужное значение из списка.

Редактирование данных

Редактировать содержимое ячейки можно в строке формул или непосредственно в ячейке. Для редактирования на месте сделайте двойной щелчок на ячейке или, выделив ее, нажмите клавишу < F2 >.

Для удаления данных из ячейки выделите ее и нажмите клавишу < Delete >. При использовании клавиши < Delete > очищается только содержимое ячейки, форматирование ее не изменяется. Команда Правка/Очистить позволяет выбрать, что именно очистить:

¨ Все - очистить все в ячейке, включая форматирование и примечания.

¨ Форматы – очистить формат выделенных ячеек, т.е. заменить формат на Общий.

¨ Содержимое – очистить только содержимое ячеек, без изменения формата и примечаний

¨ Примечания – очистить примечания выделенных ячеек.

Условное форматирование

Excel 2007 предоставляет еще более мощные и удобные инструменты условного форматирования. Такое форматирование является удобным для анализа данных - можно раскрасить рабочий лист так, что каждый цвет будет соответствовать определенным данным. В таком случае хватит даже беглого взгляда на лист документа, чтобы оценить проблемные места.

Для применения условного форматирования служит кнопка "Условное форматирование" на панели "Стили" ленты "Главная".

По умолчанию программа автоматически определяет минимальное и максимальное значение в выделенном диапазоне и затем форматирует в равных процентных соотношениях.

Если вас не устраивают параметры форматирования по умолчанию, воспользуйтесь пунктом "Другие правила" для формирования нужных правил форматирования.

Следует обратить внимание на возможность условного форматирования одной ячейки, содержащей текст, число или дату. Данный способ позволяет быстро найти ячейки с определенным значением. Для применения такого вида форматирования, необходимо выделить ячейку, нажать кнопку "Условное форматирование" и выбрать пункт "Правила выделения ячеек".

В появившемся окне задайте конкретные условия форматирования. Если не подходят стандартные операторы для форматирования, воспользуйтесь пунктом "Другие правила".

В нашем случае выделенная пустая ячейка окрасилась, т.к. по умолчанию Excel считает, что пустые ячейки эквиваленты нулевому значению. Если в ячейку ввести число большее 4, то ячейка обесцветится. Чтобы распространить условное форматирование на другие ячейки, необходимо правой кнопкой мыши потянуть за правый нижний угол только что отформатированной ячейки и в контекстном меню выбрать пункт "Заполнить только форматы".

В Excel 2007 имеется возможность условного форматирования верхних и нижних значений в диапазоне ячеек. Для этого нажмите кнопку "Условное форматирование", выберите пункт меню "Правила отбора первых и последних значений", и соответствующее правило форматирования.

В появившемся окне можно подстроить условие форматирования.

В Excel 2007 к одному и тому же диапазону можно применять одновременно несколько правил условного форматирования. Для того чтобы расставить приоритеты форматирования, необходимо воспользоваться пунктом "Диспетчер правил условного форматирования" кнопки "Условное форматирование".

Правило, находящееся вверху списка является самым приоритетным.

Для прекращения действия какого-либо правила форматирования необходимо установить флажок "Остановить, если истина".

Условные форматы можно копировать. Для копирования формата в любую ячейку (диапазон ячеек) можно воспользоваться "метелкой" "Формат по образцу", расположенной на панели "Буфер обмена" ленты "Главная".

Для удаления условного форматирования необходимо выделить нужный диапазон ячеек, нажать кнопку "Условное форматирование" и выбрать пункт "Удалить правила".

Сортировка и фильтрация

Иногда таблицы могут содержать довольно большое количество данных, причем эти данные зачастую будут представлены в виде списка. В таком случае, очень помогают в работе такие инструменты, как сортировка списков и их фильтрация. Но, при этом список должен быть оформлен определенным образом, в противном случае инструменты сортировки и фильтрации работать не будут.

Как правило, список состоит из записей (строк) и полей (столбцов). Столбцы должны содержать однотипные данные. Список не должен содержать пустых строк или столбцов. Если в списке присутствуют заголовки, то они должны быть отформатированы другим образом, нежели остальные элементы списка.

Сортировка списков

Сортировка или упорядочивание списков значительно облегчает поиск информации. После сортировки записи отображаются в порядке, определенном значениями столбцов (по алфавиту, по возрастанию/убыванию цены и пр.).

Сделайте небольшой список для тренировки.

Выделите его.

Нажмите кнопку "Сортировка и фильтр" на панели "Редактирование" ленты "Главная".

Выберите "Сортировка от А до Я". Ваш список будет отсортирован по первому столбцу, т.е. по полю ФИО.

Если надо отсортировать список по нескольким полям, то для этого предназначен пункт "Настраиваемая сортировка".

Сложная сортировка подразумевает упорядочение данных по нескольким полям. Добавлять поля можно при помощи кнопки "Добавить уровень".

В итоге список будет отсортирован, согласно установленным параметрам сложной сортировки.

Если надо отсортировать поле нестандартным способом, то для этого предназначен пункт меню "Настраиваемый список" выпадающего списка "Порядок".

Перемещать уровни сортировки можно при помощи кнопок "Вверх" и "Вниз".

Не следует забывать и о контекстном меню. Из него, также, можно настроить сортировку списка. К тому же есть такие интересные варианты сортировки, связанные с выделением того или иного элемента таблицы.

Фильтрация списков

Основное отличие фильтра от упорядочивания - это то, что во время фильтрации записи, не удовлетворяющие условиям отбора, временно скрываются (но не удаляются), в то время, как при сортировке показываются все записи списка, меняется лишь их порядок.

Фильтры бывают двух типов: обычный фильтр (его еще называют автофильтр) и расширенный фильтр.

Для применения автофильтра нажмите ту же кнопку, что и при сортировке - "Сортировка и фильтр" и выберите пункт "Фильтр" (конечно же, перед этим должен быть выделен диапазон ячеек).

В столбцах списка появятся кнопки со стрелочками, нажав на которые можно настроить параметры фильтра.

Поля, по которым установлен фильтр, отображаются со значком воронки. Если подвести указатель мыши к такой воронке, то будет показано условие фильтрации.

Для формирования более сложных условий отбора предназначен пункт "Текстовые фильтры" или "Числовые фильтры". В окне "Пользовательский автофильтр" необходимо настроить окончательные условия фильтрации.

При использовании расширенного фильтра критерии отбора задаются на рабочем листе.

Для этого надо сделать следующее.

Скопируйте и вставьте на свободное место шапку списка.

В соответствующем поле (полях) задайте критерии фильтрации.

Выделите основной список.

Нажмите кнопку "Фильтр" на панели "Сортировка и фильтр" ленты "Данные".

На той же панели нажмите кнопку "Дополнительно".

В появившемся окне "Расширенный фильтр" задайте необходимые диапазоны ячеек.

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

Расширенный фильтр удобно использовать в случаях, когда результат отбора желательно поместить отдельно от основного списка.

Связанные таблицы

Связанная таблица - это набор данных, которыми можно управлять как единым целым.

Для создания связанной таблицы предназначена кнопка "Форматировать как таблицу" на панели "Стили" ленты "Главная".

Выберите нужный стиль будущей таблицы и задайте диапазон ячеек, на основе которого будет создана связанная таблица.

После настройки всех параметров мы получим связанную таблицу. Рядом с заголовками столбцов появятся кнопки со стрелочками (по аналогии с фильтрацией), а в окне программы появится контекстный инструмент "Работа с таблицами", содержащий ленту "Конструктор".

Каждой связанной таблице дается уникальное имя. По умолчанию - "Таблица_номер". Изменить название таблицы можно на панели "Свойства".

На панели "Стили таблиц" можно, при необходимости, изменить или настроить стиль связанной таблицы.

Связанную таблицу можно обратно превратить в обычный диапазон ячеек. Для этого предназначена кнопка "Преобразовать в диапазон" на панели "Сервис" контекстной ленты "Конструктор".

Рядом с заголовками столбцов расположены кнопки сортировки и фильтрации, при помощи которых можно сортировать данные и применять фильтры.

В связанной таблице можно подсчитывать итоговые значения в столбцах. Для этого надо установить флажок "Строка итогов" на панели "Параметры стилей таблиц".

При помощи выпадающего списка можно выбрать нужное значение итоговой функции.

В связанную таблицу можно добавлять/удалять строки и столбцы.

Это можно делать несколькими способами.

1. Воспользоваться кнопкой "Изменить размер таблицы" на панели "Свойства".

2. Установить курсор в ячейке связанной таблицы, рядом с которой надо добавить новый столбец (строку) и на панели "Ячейки" ленты "Главная" и воспользоваться кнопкой "Вставить".

3. А также используя контекстное меню.

Работа с диаграммами

Для создания диаграммы необходимо воспользоваться инструментами панели "Диаграммы" ленты "Вставка".

Если не устраивает ни один из предложенных вариантов диаграмм, то необходимо воспользоваться кнопкой вызова окна панели "Диаграммы".

После этого надо указать диапазон данных для построения диаграммы. Если данные берутся из всей таблицы, то достаточно указать любую ячейку таблицы. Если надо выбрать лишь определенные данные из таблицы, то надо выделить этот диапазон. Во время выделения можно пользоваться кнопками Shift, Ctrl.

Для взаимной замены данных на осях надо воспользоваться кнопкой "Строка/Столбец".

После вставки диаграммы в окне Excel 2007 появляется контекстный инструмент "Работа с диаграммами", содержащий три ленты "Конструктор", "Макет", "Формат".

Изменение типа диаграммы

Если созданный тип диаграммы не устраивает, можно его изменить. Для этого щелкните правой кнопкой мыши в области диаграммы, затем в контекстном меню выберите команду Изменить тип диаграммы. При этом снова отображается диалоговое окно Вставка диаграммы. Выберите новый тип диаграммы, например График, после чего щелкните мышью на кнопке ОК.

Характеристики диаграмм

Гистограмма показывает изменение данных за определенный период времени и иллюстрирует соотношение отдельных значений данных.

Категории располагаются по горизонтали, а значения по вертикали. Таким образом, уделяется большее внимание изменениям во времени. Гистограмма с накоплением демонстрирует вклад отдельных элементов в общую сумму. В трехмерной гистограмме сравнение данных производится по двум осям.

Линейчатая диаграмма отражает соотношение отдельных компонентов. Категории расположены по горизонтали, а значения по вертикали, таким образом, уделяется большее внимание сопоставлению значений и меньшее – изменениям во времени. Линейчатая диаграмма с накоплением показывает вклад отдельных элементов в общую сумму.

График отражает тенденции изменения данных за равные промежутки времени.

Круговая диаграмма показывает как абсолютную величину каждого элемента ряда данных, так и его вклад в общую сумму. На круговой диаграмме может быть представлен только один ряд данных. Такую диаграмму рекомендуется использовать, когда необходимо подчеркнуть какой-либо значительный элемент. Для облегчения работы с маленькими долями диаграммы в основной диаграмме их можно объединить в один элемент, а затем разбить их в отдельную диаграмму рядом с основной.

Точечная диаграмма отображает взаимосвязь между числовыми значениями в нескольких рядах и представляет две группы чисел в виде одного ряда точек в координатах ху. Эта диаграмма отображает нечетные интервалы (или кластеры) данных и часто используется для представления данных научного характера. При подготовке данных следует расположить в одной строке или столбце все значения переменной х, а соответствующие значения переменной у- в смежных строках или столбцах.

Диаграмма с областями подчеркивает величину изменения в течение определенного периода времени, показывая сумму введенных значений. Она также отображает вклад отдельных значений в общую сумму.

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

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

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

Пузырьковая диаграмма является разновидностью точечной диаграммы. Размер маркера данных указывает значение третьей переменной. При подготовке данных следует расположить в одной строке или столбце все значения переменной х, а соответствующие значения у – в смежных строках или столбцах.

Биржевая диаграмма часто используется для демонстрации цен на акции. Этот тип диаграммы также может быть использован для научных данных, например, для определения изменения температуры. Для построения этой и других биржевых диаграмм необходимо правильно организовать данные. Биржевая диаграмма для наборов из трех и пяти значений может иметь две оси: одна для столбцов, представляющих интервал колебаний, другая для цен на акции.

Изменение источника данных

Возможно изменение источника данных для созданной прежде диаграммы. Для этого щелкните правой кнопкой мыши в области диаграммы, затем в контекстном меню выберите пункт Выбрать данные. После выполнения этой команды отобразится диалоговое окно Выбор источника данных.

В поле Диапазон данных для диаграммы указывают диапазон ячеек, служащих источником данных для диаграммы.

На панели Элементы легенды (ряды) можно добавить новые записи, отредактировать либо вовсе удалить прежние записи.

На панели Подписи горизонтальной оси (категории) можно удалить или изменить надписи на горизонтальной оси диаграммы.

Кнопка "Строка/Столбец" используется для взаимной замены данных на осях.