Списки и базы данных MicrosoftExcel

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

Список должен отвечать ряду требований: список целиком помещается на одном рабочем листе, максимальный размер списка – более 1 млн. строк и 16,4 тыс. столбцов; список должен от других данных рабочего листа хотя бы одним незаполненным столбцом и/или строкой; имена столбцов списка располагаются в первой строке таблицы, имена могут быть многострочными с переносами слов; Традиционные операции со списками:

Ввод и редактирование данных списка. Сортировка – упорядочивание записей списка Фильтрация записей списка по условиям

Агрегирование информации списка, вычисление статистических оценок

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

Консолидация – объединение данных нескольких списков с формированием общих итогов.
Особенностью списков является то, что они сортируются построчно, другие же таблицы могут сортироваться как по строкам, так и по столбцам. Результат сортировки списка всегда остается на одном и том же месте. Для сортировки можно задать от 1 до 64 ключей с указанием направления сортировки – по возрастанию или убыванию для каждого ключа в отдельности. Если данные в ячейках имеют смешанный формат, установлены приоритеты: числа; текст; логические значения; значения ошибок; пустые ячейки.

Различают 2 способа фильтрации записей списка в команде меню Данные =>Сортировка и фильтр => Фильтр и Расширенный фильтр.

Фильтр создаёт для каждого поля список значений, используемый для отбора записей:

Все – нет ограничений на значения поля.

Условие – формирование условий отбора в Пользовательскомавтофильтре

Конкретное значение – отбор записей с указанным значением поля.

Пустые – в поле нет значения

Непустые – в поле содержится какое-либо значение.

Условия для первого поля фильтрует записи списка MicrosoftExcel. Условия для второго поля фильтрует результат предыдущей фильтрации и т.д.

Расширенный фильтр предполагает формирование специального диапазона ячеек в произвольном месте. Этот диапазон является критерием для расширенного фильтра списка. Диапазон условий состоит из имен полей списка и строк условий.

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

Для формирования промежуточных итогов требуется предварительная сортировка списка по полям группировки записей, например, по полям Поле1, Поле2, Поле3. Команда меню Данные => Промежуточные итоги позволяет сформировать итоги определенного вида по указанным полям при каждом изменении поля группировки- Поле1( или Поле2 или Поле3).

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

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

Различают 2 вида консолидации:

Консолидация по расположению ячеек – состав и порядок следования консолидируемых данных во всех диапазонах постоянный;

Консолидация по категориям – на основании одинаковых подписей строк и столбцов для диапазона ячеек.

Информационные технологии поиска решений

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

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

Сценарий – именованный набор значений указанных ячеек листа рабочей книги. Сценарный подход обеспечивает решение задач типа «ЧТО ЕСЛИ», не ограничивая число одновременно изменяемых параметров для каждого такого набора. Сценарий используется для постановки значений параметров в ячейки таблицы и вычисления зависящих от них формул.

Информационная технология таблицы постановки используется для изучения влияния значений параметра на результат нескольких функций либо значений двух параметров на результат одной функции. Эта информационная технология обеспечивает решение задач анализа типа «ЧТО ЕСЛИ», ограничивая число одновременно изменяемых параметров модели.

Информационная технология поиска решений используется для реализации оптимизационных расчетов в MicrosoftExcel. Модель задачи задаётся в диалоговом окне Поиск решения. Модель использует целевую функцию, которая записывается в виде формулы в отдельной ячейке. Для целевой функции указывается: максимизация, минимизация или равенство фиксированному значению. В процессе поиска решения изменяются значения в указанных ячейках, соответствующих переменным, при соблюдении ограничений. Если решение найдено, его можно сохранить, либо восстановить исходные значения переменных. По результатам решения создаются отчеты.