Тема 4.4

Возможности динамических (электронных) таблиц.

1. ЭТ как информационный объект: характерные особенности, назначение

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

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

К обработке данных относится:

проведение различных вычислений с помощью формул и функций, встроенных в редактор;
построение диаграмм;
обработка данных в списках (Сортировка, Автофильтр, Расширенный фильтр, Форма, Итоги, Сводная таблица);
решение задач оптимизации (Подбор параметра, Поиск решения, Сценарии "что - если" и другие задачи);
статистическая обработка данных, анализ и прогнозирование (инструменты анализа из надстройки "Пакет анализа").

Область применения Excel: планово – финансовые и бухгалтерские расчеты, учет материальных ценностей, системы поддержки принятия решений (СППР) и другие области применения.

Создание новой рабочей книги в Excel

Обучение работе с Excel необходимо начинать с изучения окна приложения Excel. При запуске Excel открывается окно приложения, в котором отображается новая рабочая книга – Книга 1.

Окно приложения Excel имеет пять основных областей:

строка меню;
панели инструментов;
строка состояния;
строка ввода;
область окна рабочей книги.

Основная обработка данных в Excel осуществляется при помощи команд из строки меню. Панели инструментов Стандартная и Форматирование являются встроенными панелями MS Excel, которые располагаются под строкой меню и содержат определенные наборы пиктограмм (кнопок). Основная часть пиктограмм предназначена для выполнения наиболее часто используемых команд из строки меню.
Строка формул в Excel используется для ввода и редактирования значений, формул в ячейках или диаграммах. Поле имени – это окно слева от строки формул, в котором выводится имя активной ячейки. Пиктограммы: X, V, fx, расположенные слева от строки формул - это кнопки отмены, ввода и вставка функции соответственно.
Строка состояния окна приложения Excel расположена в нижней части экрана. Левая часть строки состояния указывает сведения о состоянии рабочей области электронной таблицы (Готово, Ввод, Правка, Укажите). Кроме того, в левой части строки состояния кратко описываются результаты выполненной команды. В правой части строки состояния выводятся результаты вычислений (при выполнении автоматических вычислений с помощью контекстного меню строки состояния) и отображаются нажатые клавиш Ins, Caps Lock, Num Lock, Scroll Lock.
Основные понятия электронной таблицы: заголовок столбца, заголовок строки, ячейка, имя ячейки, маркер выделения, маркер заполнения, активная ячейка, строка формул, поле имени, активная область листа.
Рабочая область электронной таблицы состоит из строк и столбцов, имеющих свои имена.
Имена строк – это их номера. Нумерация строк начинается с 1 и заканчивается максимальным числом, установленным для данной программы. Имена столбцов – это буквы латинского алфавита сначала от А до Z , затем от АА до AZ, ВА до BZ и т.д.
Максимальное количество строк и столбцов электронной таблицы определяется особенностями используемой программы и объемом памяти компьютера, например, в табличном процессоре Excel 256 столбцов и более 16 тысяч строк. В таблице используются столбцы (256) и строки (16384).
Пересечение строки и столбца образует ячейку электронной таблицы, имеющую свой уникальный адрес. Для указания адресов ячеек в формулах используются ссылки (например, А6 или D8).
Ячейка – область, определяемая пересечением столбца и строки электронной таблицы, имеющая свой уникальный адрес.
Адрес ячейки определяется именем (номером) столбца и именем (номером) строки, на пересечении которых находится ячейка, например А10. Ссылка – указание адреса ячейки.
Активной ячейка - это выделенная ячейка, имя которой отображается в поле имени. Маркером выделения называется полужирная рамка вокруг выделенной ячейки. Маркер заполнения - это черный квадрат в правом нижнем углу выделенной ячейки.
Активная область листа - это область, которая содержит введенные данные.
Адрес блока ячеек задается указанием ссылок первой и последней его ячеек, между которыми ставится разделительный символ – двоеточие. Если блок имеет вид прямоугольника, то его адрес задается адресами левой верхней и правой нижней ячеек, входящих в блок.
Блок используемых ячеек может быть указан двумя путями либо заданием с клавиатуры начального и конечного адресов ячеек блока, либо выделением соответствующей части таблицы при помощи левой клавиши мыши.

Пример задания адресов ячейки и блоков в электронной таблице:

адрес ячейки, находящейся на пересечении столбца F и строки 9, выражается ссылкой F9;
адрес блока, образованного в виде части строки 1 - B1:E1;
адрес блока, образованного в виде столбца C - C1:C21;
адрес блока, образованного в виде прямоугольника - A3:G10

Формулы – это выражение, начинающееся со знака равенства и состоящее из числовых величин, адресов ячеек, функций, имен, которые соединены знаками арифметических операций. К знакам арифметических операций, которые используются в Excel относятся: сложение; вычитание; умножение; деление; возведение в степень.
Некоторые операции в формуле имеют более высокий приоритет и выполняются в такой последовательности:

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

2. Применение ЭТ для решения профессиональных задач. Использование функций

Области применения электронных таблиц:

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

Основные функции, встроенные в MS Excel

Математические функции
ABS(число) – возвращает модуль числа;
SIN(число), COS(число), TAN(число) – прямые тригонометрические функции числа;
ASIN(число), ACOS(число), ATAN(число) – обратные тригонометрические функции числа;
EXP(число), LN(число), LOG10(число) – экспонента, натуральный логарифм, десятичный логарифм числа;
ФАКТР(число) – возвращает значение факториала числа;
LOG(число;основание) – логарифм от числа по основанию;
КОРЕНЬ – вычисление квадратного корня;
ОКРУГЛ(число;число_разрядов) – округляет число до указанного числа разрядов;
ПИ() – выдает значение числа;
СУММ(диапазон_ячеек) – считает сумму значений из диапазона ячеек;
ПРОИЗВЕД(диапазон_ячеек) – считает произведение значений из диапазона ячеек;
СТЕПЕНЬ(число;степень) – возвращает результата возведения числа в степень;
СЛУЧМЕЖДУ(нижн_граница;верхн_граница) – выдает случайное число между нижней границей и верхней границей (изменяется при пересчете);
СЛЧИС() – возвращает случайное число от 0 до 1 (изменяется при пересчете);
СУММЕСЛИ(диапазон;критерий;диапазон_суммирования) – суммирует значения, расположенные в диапазоне суммирования, если соответствующие ячейки диапазона соответствуют указанному критерию; критерии: “>10”, “=100”, “=Сумма”;
МОПРЕД(диапазон) – возвращает определитель матрицы, расположенной в диапазоне ячеек (диапазон должен быть квадратной матрицей);
МОБР(диапазон) – возвращает обратную матрицу для матрицы, заданной в диапазоне (диапазон должен быть квадратной матрицей); для отображения результата нужно выделить диапазон ячеек равный исходному диапазону начиная с ячейки, где расположена функция МОБР, затем нажать F2, затем нажать комбинацию CTRL+SHIFT+ENTER (работа с массивами данных);
МУМНОЖ(диапазон1; диапазон2) – возвращает матрицу, полученную в результате перемножения матриц, заданных в диапазоне1 и диапазоне2 (работа с массивами, выделяемые диапазоны должны удовлетворять требованиям перемножения матриц).

Логические функции
ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь) – проверяет, выполняется ли логическое выражение, если да, то выводит значение если истина, нет – значение если ложь;
И(логич_знач1;логич_знач2;…) – возвращает значение ИСТИНА, если все логические значения являются истинными, в противном случае возвращает значение ЛОЖЬ;
ИЛИ(логич_знач1;логич_знач2;…) – возвращает значение ИСТИНА, если хотя бы одно логическое значение является истинными, в противном случае возвращает значение ЛОЖЬ.

Статистические функции
МИН(диапазон), МАКС(диапазон) – возвращают минимальное и максимально значение из диапазона;
СРЗНАЧ(диапазон) – вычисляет среднее арифметическое значение из выбранного диапазона значений;
СРГЕОМ(диапазон) – вычисляет среднее геометрическое значение из выбранного диапазона значений;
СЧЁТ(диапазон) – подсчитывает количество чисел в указанном диапазоне значений;
СЧЁТЗ(диапазон) – подсчитывает число непустых ячеек в указанном диапазоне данных;
СЧЁТЕСЛИ(диапазон;критерий) – подсчитывает число непустых ячеек в диапазоне, удовлетворяющих заданному критерию;
ЧАСТОТА(диапазон_данных;диапазон_интервалов) – вычисляет распределение значений из диапазона данных по интервалам диапазона интервалов и возвращает вертикальный массив (работа с массивами); интервалы определяются так: первый – все значения до первого числа диапазона интервалов, второй – от первого значения диапазона интервалов +1 до второго значения диапазона интервалов…, последний – от последнего значения диапазона интервалов +1 и больше.