Списком в Excel принято называть массив данных со столбцами и строками (прямоугольную таблицу). Он может использоваться как база данных, в которой строки являются отдельными записями, а столбцы являются полями. Они описывают отдельные параметры записей строк. Первая строка всегда содержит названия столбцов (полей). Каждая запись должна содержать полное описание каждого элемента, при этом не разрешается иметь пустых клеток, так как это затрудняет операции со списком. Количество полей в каждой записи одинаково. Каждое поле записи может являться объектом поиска или сортировки.
Ведение таких небольших таблиц может быть организовано напрямую записью всех полей каждой строки. С увеличением же их размеров (числа столбцов и числа записей в них) пользоваться ими становится труднее, так как при записи уходят с поля экрана названия полей (столбцов и строк), что может привести к ошибкам во вводе данных. Более удобно использовать запись в форму. Форма позволяет проводить и другие операции с записями. Форма работы с записями называется маской, так как в ней отображаются не таблица в целом, а ее отдельные поля, с записями в которых и организуется работа.
Создание списка (базы данных) начинается с организации таблицы. На каждом рабочем листе должен размещаться один список. Если на листе будет несколько списков, то затрудняется, а иногда и становится невозможной работа с ними.
Между списком и другими данными листа должно быть оставлено не менее одной пустой строки и одного пустого столбца, что позволяет программе обнаруживать и выделять список при операциях с ним. В самом списке не должно быть пустых строк или столбцов, что позволяет идентифицировать и выделить список. Во всех строках одинаковых столбцов должны быть однотипные данные.
При вводе данных в ячейки не допускается вводить лишние элементы, не связанные с данными, например, лишние пробелы.
Создание списка с помощью формы:
Сформировать заглавную строку, введя в нее название записей отдельных столбцов.
Щелкнуть на любой ячейке заглавной строки и выбрать команду Данные►Форма.
В открывшемся диалоговом окне, которое содержит отдельные поля списка с их названиями, ввести данные в каждое поля. Для перехода между полями можно использовать либо клавишу мыши, либо клавиши Tab – для перехода сверху вниз и Shift+Tab – для перехода снизу вверх.
После ввода каждой записи нажимать кнопку Добавить, при этом введенная запись помещается в очередную строку списка.
Для завершения ввода всех записей нажать кнопку Закрыть.
Для редактирования записей в списке, добавлении новых записей:
Вызвать форму, установив курсор в любую ячейку формы и подав команду Данные►Форма.
Используя кнопки Назад и Далее, найти требуемую запись, отредактировать ее в строках формы и подать команду Закрыть.
Для удаления записи выбрать ее и нажать кнопку Удалить, подтвердить удаление нажатием кнопки Ок и закрыть форму.
При добавлении новых записей в форму они размещаются позади последней введенной ранее записи. Для ввода новой записи внутри существующего списка необходимо список сначала раздвинуть, вставив внутрь его пустую строку для новой записи. Для этого установить курсор в ячейку строки, выше которой необходимо ввести запись, и подать команду Вставка►Добавить. После этого заполнить поля строки данными.
Часто в созданной таблице требуется изменить порядок представления данных. Данные можно располагать в порядке возрастания или убывания или в алфавитном или обратном алфавитном порядке. Для этого используется команда Данные►Сортировка либо возможности стандартной панели.
1.При использовании команды Данные►Сортировка:
Выделить диапазон ячеек таблицы, данные в котором должны сортироваться;
Задать команду Данные►Сортировка. Появится панель с указанием условий сортировки;
Если выделена строка с шапкой (заголовками строк), то в панели включить параметр Идентифицировать поля по подписям, если шапка не выделена, то включить параметр Идентифицировать поля по обозначениям столбцов листа;
В верхнем поле Сортировать указать адрес (название метки столбца), первого ключа сортировки. Если требуется сортировать по другим столбцам, то вести эту сортировку, определив очередность использования других столбцов и вид сортировки (по возрастанию или убыванию).
2. Сортировка с помощью стандартной панели:
Выделить диапазон данных, подлежащих сортировке, начиная с ячейки той колонки, которая должны использоваться в качестве ключевой. Эта колонка должны быть крайней в диапазоне;
Нажать кнопку панели Сортировка. В этом случае будет выведена панель условий сортировки. Далее все выполняется так же, как в предыдущем случае. При использовании кнопок Сортировка по возрастанию или Сортировка по убыванию панель не выводится, но сортировка производится только по одному первому столбцу выделенного диапазона.
Хотя в панели сортировки используется только три уровня сортировки, реально же их количество может быть увеличено, если провести сортировку несколько раз, используя в качестве объекта сортировки диапазон ячеек таблицы, в котором ранее уже проведена сортировка.
Сортировка числовых данных проводится по алфавиту, но команда сортировки подается по убыванию или возрастанию. Дело в том, что все буквенные записи хранятся в ячейках памяти ЭВМ в виде двоично-кодированных цифр. Буквам, находящимся в начале алфавита, приписывается код, числовое значение которого меньше. У каждой очередной буквы код увеличивается на единицу. Это позволяет и сортировку по алфавиту задавать как сортировку по возрастанию.
Итоговые данные учитывают все результаты данных, отраженных в таблице. Промежуточные итоги подводятся по части данным, по отдельным их категориям, например, результаты поставки отдельных видов товаров, работы отдельных категорий сотрудников, расходов на отдельные виды деятельности. При определении промежуточных результатов используют следующий алгоритм работы:
Сортируют данные таблицы по столбцам, которые содержат группы, используемые при подведении промежуточных итогов;
Установив курсор в любую ячейку этого столбца, задают команду Данные►Итоги;
В поле При каждом изменении указывают столбец с группами, по которым подводят итоги;
В поле Использовать функцию указывается функция, которая определяется при подведении итогов (например, СУММ). В перечне Добавить итоги по указывают столбцы, значения в которых должны приниматься в расчет при определении результата;
Нажать кнопку Ок.
Для скрытия или показа входящих в результат промежуточных данных необходимо нажать кнопку с номером уровня, при этом чем выше номер, тем более детально показывается промежуточная информация. Для скрытия детализирующих данных по какой-либо группе необходимо нажать кнопку «-» (минус) слева от данной группы. Если же нажать кнопку «+» (плюс) слева от группы, то выводится информация, уточняющая данные по этой группе.
Для удаления полученных промежуточных итогов установить курсор в любую ячейку столбца с группами итогов, задать команду Данные►Итоги и нажать кнопку Убрать все.
Задача анализа данных состоит в подборе таких параметров (величин) данных, при которых обеспечивается достижение некоторой цели (результата). Обычно цель задается в виде формулы, тогда подобранные параметры данных должны обеспечить ее выполнение наилучшим образом.
Математически задача состоит в решении уравнения f(x)=a, где функция f(x) описывается формулой, x является исходным параметром, значение которого требуется подобрать, a – требуемый результат ее решения.
Решение проблемы ищется в соответствии с алгоритмом:
В ячейку, где должен быть получен результат подбора, записывается формула, которая требует своего решения;
В меню Сервис выбирается команда Подбор параметра.
В поле Установить в ячейке вводится ссылка на ячейку, содержащую формулу (по умолчанию, в эту ячейку вводится адрес текущей ячейки);
В поле Значение ввести значение, которое нужно получить при решении задачи;
В поле Изменяя ячейку вести ссылку на ячейку, где находится значение изменяемого параметра (аргумента);
Щелкнуть клавишу Ок.
При вводе формулы указывается адрес ячейки, где хранится значение изменяемого параметра (аргумента).
После выполнения команды в ячейке с аргументом появится его подобранное значение, а в ячейке с формулой – определенное при этом значение вычисляемой величины (функции). При этом в таблице будут пересчитаны значения всех ячеек, которые влияют на или зависят от величины изменяемого параметра.
В среде Excel можно хранить наборы значений данных, которые приводят к различным результатам. Это полезно, когда есть необходимость проанализировать (сравнить) результативность нескольких разных наборов исходных данных (ситуаций). Сохранение данных производится в виде сценария.
Сценарий – это множество входных значений, которые называют изменяемыми ячейками. Их можно сохранит под некоторым именем и применить впоследствии к модели рабочего листа, чтобы проследить влияние изменения значений изменяемых ячеек на другие значения модели. Для каждого сценария можно определить для хранения данных до 32 ячеек.
Для создания сценария:
В меню Сервис выбрать команду Сценарии;
Щелкнуть по кнопке Добавить. Открывается окно Добавление сценария;
В поле Название сценария необходимо ввести имя нового сценария;
В поле Изменяемые ячейки ввести ссылки на изменяемые ячейки. Ссылки на отдельные ячейки отделяются точкой с запятой, их можно ввести с клавиатуры или путем выделения ячеек на рабочем листе. Несмежные ячейки выделяются при нажатой кнопке Ctrl;
Согласиться с введенными данными (щелчок по кнопке Ок);
В открывшемся диалоговом окне Значения ячеек сценария ввести значения каждой изменяемой ячейки;
Для создания других сценариев щелкнуть по кнопке Добавить, при этом открывается диалоговое окно Добавление сценария. Повторить процедуру ввода данных для нового сценария;
Для завершения работы с Диспетчером сценариев щелкнуть по кнопке Ок, потом – по кнопке Закрыть.
Чтобы можно было быстро восстановить значения таблицы, используемой для поиска решения, целесообразно сохранить ее исходные данные в виде сценария.
Для просмотра сценария:
Подать команду Сервис Сценарии;
В поле Сценарии выделить имя сценария, который требуется просмотреть:
Щелкнуть по кнопке Вывести.
Чтобы отредактировать сценарий (изменить исходные значения записанных в нем параметров):
Подать команду Сервис Сценарии;
В поле Сценарии выделить имя сценария для редактирования;
Щелкнуть по кнопке Изменить;
Внести изменения в сценарий (его имя, адреса изменяемых ячеек и их значения);
Для завершения работы с Диспетчером сценариев щелкнуть по кнопке Ок, потом – по кнопке Закрыть.
Для создания итогового отчета по сценариям:
Выбрать команду Сервис Сценарии;
Щелкнуть по кнопке Отчет;
Выбрать тип отчета: структура или сводная таблица;
В отчете Структура перечислены все сценарии с определенными для них значениями ячеек. Такой отчет полезен, когда каждый пользователь определяет сценарий со своими данными;
Отчет типа Сводная таблица позволяет провести анализ имеющихся сценариев. Можно проанализировать результаты проведения поиска решения по разным наборам изменяющихся ячеек, провести анализ для разных комбинаций сценариев;
В поле Ячейки результата ввести ссылки на ячейки, значения которых надо представить в отчете. В качестве разделителя ссылок используется запятая. Ссылки можно ввести с клавиатуры или выделить на рабочем листе. Несмежные ячейки вводятся при нажатой клавиш Ctrl. Итоговые таблицы создаются на отдельных листах.
.