Технология обработки табличных данных в MSExcel
Microsoft Excel (MS Excel) - табличный процессор или программа для работы с электронными таблицами.[5]
Табличный процессор MS Excel обеспечивает работу с большими таблицами чисел. При работе с табличным процессором на экран выводится прямоугольная таблица, в клетках которой могут находиться числа, пояснительные тексты и формулы для расчета значений в клетке по имеющимся данным. То есть программные средства для проектирования электронных таблиц называют табличными процессорами. Они позволяют не только создавать таблицы, но и автоматизировать обработку табличных данных. С помощью электронных таблиц можно выполнять различные экономические, бухгалтерские и инженерные расчеты, а также строить разного рода диаграммы, проводить сложный экономический анализ, моделировать и оптимизировать решение различных хозяйственных ситуаций. [6]
В MSExcel существуют функции категории «Дата и время». При решении задач в период учебной практики использовались функции категории «Дата и время», представленные в таблице 2.1.
Таблица 2.1 – Функции категории «Дата и время»
| Синтаксис | Описание |
| ДАТА(год;месяц;день) | Возвращает значение даты, составленное из отдельных частей, заданных числами |
| ГОД(дата_в_числовом_формате) | Возвращает год, соответствующий заданной дате. Год определяется как целое число в диапазоне от 1900 до 9999 |
| ДЕНЬ(дата_в_числовом_формате) | Возвращает день даты, заданной в числовом формате. День возвращается как целое число в диапазоне от 1 до 31 |
| Продолжение таблицы 2.1 – Функции категории «Дата и время» | |
| Синтаксис | Описание |
| ТДАТА() | Возвращает текущую дату и время в числовом формате |
| СЕГОДНЯ() | Возвращает текущую дату без времени. |
| ДЕНЬНЕД(дата_в_числовом_формате;[тип]) | Возвращает день недели, соответствующий дате. По умолчанию день недели определяется как целое число в интервале от 1 (воскресенье) до 7 (суббота) |
| МЕСЯЦ(дата_в_числовом_формате) | Возвращает месяц для даты, заданной в числовом формате. Месяц возвращается как целое число в диапазоне от 1 (январь) до 12 (декабрь) |
| ВРЕМЯ(часы;минуты;секунды) | Возвращает десятичное число, представляющее определенное время. Если до ввода этой функции для ячейки был задан формат Общий, результат будет отформатирован как дата |
| ЧИСТРАБДНИ (нач_дата;кон_дата;[праздники]) | возвращает количество полных рабочих дней между двумя датами |
| РАБДЕНЬ (нач_дата;количество_дней;[праздники]) | возвращает дату в числовом формате, отстоя-щую вперед или назад на заданное количество рабочих дней |
| ЧАС (время_в_чисвом_формате) | преобразует дату в числовом формате в часы |
| МИНУТЫ (вре-мя_в_числовом_формате) | преобразует дату в числовом формате в мину-ты |
Таблица 2.1 составлена с помощью справочника по функциям MSExcel.Некоторые функции требуют подключения надстройки «Пакет анализа». Примеры решенных задач с использованием функций данной категории представлены в файле «П141 Акулинина Функции ДАТА И ВРЕМЯ» в папке «MSExcel» на диске 1.
В период учебной практики при решении задач использовались встроенные функции MS Excel для работы с матрицами. В MS Excel для матричного умножения используется встроенная функция «МУМНОЖ(массив1; массив2)». Нахождение определителя матрицы выполняет встроенная функция «МОПРЕД(массив)». Нахождение обратной матрицы выполняет встроенная функция «МОБР(массив)». Некоторые функции требуют подключения надстройки «Пакет анализа». Примеры решенных задач с использованием функций данной категории представлены в файле«П141 Акулинина Тема3»в папке «MSExcel» на диске 1.
В ходе учебной практики при решении линейных задач средствами Excel, использовалась надстройка «Поиск решения». Чтобы включить надстройку, необходимо перейти по вкладке «Файл» в группу «Параметры». В появившемся диалоговом окне «Параметры», нужно выбрать «Надстройки», далее «Управление: Надстройки Excel». С помощью этой надстройки можно найти оптимальное значение (максимум или минимум). Формула, содержащейся в одной ячейке, называемой целевой, с учетом ограничений на значения в других ячейках с формулами на листе. Для решения задач линейного программирования необходимо запустить надстройку «Поиск решения», установить нужные параметры решения и запустить выполнение. Программа подберет оптимальное решение, выдаст отчеты для анализа решения задачи. [7]
Примеры решенных задач с использованием функций данной категории представлены в файле «П141 Акулинина Линейная функция» в папке «MSExcel» на диске 1.
В период учебной практики были решены оптимизационные и транспортные задачи. Транспортная задача (классическая) — задача об оптимальном плане перевозок однородного продукта из однородных пунктов наличия в однородные пункты потребления на однородных транспортных средствах (предопределённом количестве) со статичными данными и линеарном подходе (это основные условия задачи).
Когда суммарный объём предложений (грузов, имеющихся в пунктах отправления) не равен общему объёму спроса на товары (грузы), запрашиваемые пунктами потребления, транспортная задача называется несбалансированной (открытой). Если транспортная задача открытого типа то, в этой задачи необходимо добавить дополнительную строку, то есть сбалансировать, в случае, если сумма запасов меньше суммы потребностей или дополнительный столбец, в случае, если сумма запасов больше чем сумма потребностей.
Для решения транспортных задач, необходимо ввести данные в ячейки таблицы. Целевой функцией будет «СУММПРОИЗВ(массив1;[массив2];[массив3];…)», которая перемножает соответствующие элементы заданных массивов и возвращает сумму произведений. Далее нужно запустить надстройку «Поиск решения», которая подберет оптимальное решение транспортной задачи.
Примеры решенных задач с использованием функций данной категории представлены в файле «П141 4.4 Акулинина» в папке «MSExcel» на диске 2. [8]
В ходе учебной практики использовались логические функции для решения задач в MS Excel. Например, функция «ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])», которая позволяет выполнять логические сравнения значений и ожидаемых результатов, функция «ИСТИНА()», функция «ЛОЖЬ()», функция «И(логическое_значение1;[логическое_значение2];…)», а также функция «ИЛИ(логическое_значение1;[логическое значение2];...)». Примеры решенных задач с использованием функций данной категории представлены в файле «П141 Тема 5 Акулинина» в папке «MS Excel» на диске 2.
Закон распределения случайной величины - это соотношение, устанавливающее связь между возможными значениями случайной величины и вероятностями появления их в испытании. Для того чтобы построить график распределения случайной дискретной (непрерывной) величины необходимо использовать логическую функцию «ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])» и табулирование функций. Примеры решенных задач с использованием функций данной категории представлены в файле «П141 Тема6 Акулинина» в папке «MS Excel» на диске 2.
Средствами Excel возможно построение линии тренда и составление прогноза. Линии тренда – это линии, проводимые под углом, над или под ценой, которые служат для обозначения, развивающегося в настоящий момент тренда, а также для определения момента их разворота. Для построения графика функции тренда на листе с таблицей данных необходимо ввести не менее двух ячеек диапазона, для которого будет построен график, и вставить диаграмму. Функция «ТЕНДЕНЦИЯ(известные_значения_y; [известные_значения_x]; [новые_значения_x]; [конст])», которая предназначена для того чтобы рассчитать значения тренда для всего временного диапазона.[9]
Примеры решенных задач с использованием функций данной категории представлены в файле «П141 Тема7 Акулинина»в папке «MS Excel» на диске 2.
В период учебной практики были решены задачи, с использованием функций массивов и ссылок, таких как, функция «ГПР(ключ; диапазон; номер_строки; [интервальный_просмотр])» (горизонтальное первое равенство) и функция «ВПР(ключ; диапазон; номер_столбца; [интервальный_просмотр])» (вертикальное первое равенство), которые ищут совпадение по ключу в первом столбце определенного диапазона и возвращает значение из указанного столбца этого диапазона в совпавшей с ключом строке, а также функция «ПОИСКПОЗ(искомое_значение; мас-сив; [тип_сопоставления])», которая возвращает позицию элемента, заданного по значению, в диапазоне либо массиве и функция «ПРОСМОТР(искомое_значение; просматриваемый_вектор; [вектор_результатов])». [10]
Примеры решенных задач с использованием функций данной категории представлены в файле «П141 тема8 Акулинина» в папке «MS Excel» на диске 2.
MS Excel содержит средства формирования сводной информации для проведения анализа данных.
Сводными называются таблицы, содержащие часть данных анализируемой таблицы, показанные так, чтобы связи между ними отображались наглядно. Сводная таблица создается на основе отформатированного списка значений. Поэтому, прежде чем создавать сводную таблицу, необходимо подготовить соответствующим образом данные.
Консолидация данных позволяет создать таблицу-сводку по одной или нескольким категориям данных, используя один или несколько блоков исходных данных. При выполнении консолидации пользователь должен задать тип функции, по которой будут вычисляться итоговые значения. [11]
Примеры решенных задач с использованием функций данной категории представлены в файле «П141 Пров9и10 Акулинина» в папке «MS Excel» на диске 2.
Для более удобного поиска информации в базе данных используются сортировка и фильтрация. Сортировка данных - это средство, интегрированное в анализ данных. Может понадобиться расположить в алфавитном порядке фамилии в списке или составить перечень продуктов в порядке убывания их количества на складе. Существует два способа фильтрации данных. Авто-фильтр и расширенный фильтр в Excel:
1. автофильтр, предназначен для наиболее простых операций;
2. расширенный фильтр в MS Excel очень мощный инструмент для отбора нужных данных.
Примеры решенных задач с использованием функций данной категории представлены в файле «П141 Тема10 Акулинина»в папке «MS Excel» на диске 2.
Введение в базу данных
При компьютерной обработке информации упорядоченные каким-либо образом данные принято хранить в базах данных - особых файлах, использование которых вместе со специальными программными средствами позволяет пользователю как просматривать необходимую информацию, так и, по мере необходимости, манипулировать ею, например, добавлять, изменять, копировать, удалять, сортировать и т.д.
Информация – это сведение о объектах, явлениях, процессах, событиях окружающего мира, уменьшающая неопределенность знаний о них.
Информационная система – совокупность технических и программных средств, обеспечивающих сбор, хранение, обработку, поиск и выдачу информации в задачах любой области.
Информационный объект – описание, некоторой сущности предметной области (объекты, процессы, явления).
Данные – информация, зафиксированная в некоторой форме, которая пригодна для последующей обработки передачи, хранении и т.д.
Методанные – описание, собственной структуры БД. Данные о данных.
Система управления БД (СУБД) – совокупность, языковых и программных средств, предназначенных для управления созданием и использованием БД. [12]
Организация баз данных
Предметная область – это часть реального мира, данные о которых хранятся и используются в информационной системе.
База данных (БД) – это именованная совокупность, взаимосвязанных, используемых несколькими пользовании данных, которые отражают состояние объектов и их отношения, в некоторой предметной области.
Банк данных – это система, специальным образом организационных данных БД, программных, языковых, организационно методических средств, предназначенных для обеспечения централизованного накопления и коллективного многоцелевого использования данных.
Этапы проектирования баз данных:
1. Концептуальное проектирование - сбор, анализ и редактирование требований к данным. Для этого осуществляются следующие мероприятия:
обследование предметной области, изучение ее информационной структуры, выявление всех фрагментов, каждый из которых характеризуется пользовательским представлением, информационными объектами и связями между ними, процессами над информационными объектами, моделирование и интеграция всех представлений
По окончании данного этапа получаем концептуальную модель, инвариантную к структуре базы данных. Часто она представляется в виде модели "сущность-связь".
2. Логическое проектирование - преобразование требований к данным в структуры данных. На выходе получаем СУБД-ориентированную структуру базы данных и спецификации прикладных программ. На этом этапе часто моделируют базы данных применительно к различным СУБД и проводят сравнительный анализ моделей.
3. Физическое проектирование - определение особенностей хранения данных, методов доступа и т.д.[12]