Технологии табличных вычислений

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

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

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

Рабочее поле электронной таблицы состоит из столбцов и строк.

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

Пример. А1 – адрес самой первой ячейки.

Ячейка, в которой находится курсор и выделена рамкой, называется активной.

Воздействовать можно только на активную ячейку.

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

Пример. А1:D4.

Вычисление в таблицах с помощью формул

Содержимое ячейки может являться текстом, числом, формулой. Чтобы составить последнюю нужно иметь в виду:

1. В электронных таблицах каждая формула начинается со знака «равно».

2. Арифметические операции в формулах:

Пример. Чтобы найти 2⁸, запишем в ячейку формулу:

Типы ссылок

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

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

Знаком фиксации служит «$», который ставится перед буквой и номером строки в адресе ячейки.

Пример. $A$2 – абсолютная ссылка на ячейку A2.

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

Пример. А2 – относительная ссылка на ячейку А2.

В смешанных ссылках мы фиксируем либо только строку, либо столбец.

Пример. $A2 – смешанная ссылка на ячейку А2 с зафиксированным столбцом.

A$2 – смешанная ссылка на ячейку А2 с зафиксированной строкой.

Теперь с помощью ссылок создадим таблицу степеней двоек.

Пример. Для этого во вторую строку копируем формулу, в которой абсолютно ссылаемся на ячейку A2 (то есть на основание, равное двум), а степень в формуле – смешанная ссылка с зафиксированной строкой.

Встроенные функции

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

Создать функцию можно несколькими способами:

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

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

Написать в ячейке знак «=» и воспользоваться списком последних используемых функций.

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

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

Математические и статистические функции

Рассмотрим основные математические и статистические функции.

СУММ - Находит сумму всех аргументов.

Пример. В ячейку С5 записали сумму значений ячеек А1:C3.

СРЗНАЧ - Считает среднее арифметическое аргументов.

Важно, что при расчёте среднего арифметического НЕ учитываются пустые ячейки.

Пример. В ячейку С5 записали среднее значение ячеек А1:C3.

СЧЁТ – Считает количество НЕПУСТЫХ ячеек в диапазоне.

Пример. В ячейку С5 записали формулу =СЧЁТ(А1:C3).

Полученное значение в ячейке С5 равно именно 7, так как в выделенном диапазоне 7 заполненных ячеек, если мы запишем ещё в одну ячейку какое-то значение (даже 0), то значение в С5 увеличится на 1.

МАКС - Возвращает наибольшее значение в списке аргументов.

МИН - Возвращает наименьшее значение в списке аргументов.

Пример. В ячейку С5 записали формулу =МАКС(А1:C3), а в ячейку B5 = МИН(А1:C3).

СУММЕСЛИ - Суммирует значения в ячейках, удовлетворяющие заданному условию.

Пример. В ячейку С5 необходимо сосчитать сумму значений, больших 1, в диапазоне А1:C3.

При вызове диалогового окна функции заполняем выбор диапазона и записываем условие.

Получаем в ячейке формулу =СУММЕСЛИ(A1:C3;">1"), и результат равный 11 (2+3+3+3)

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

Пример. В ячейку С5 запишем формулу =СУММЕСЛИ(A1:A3;">1";B1:B4).

Условие проверялось по столбцу А, а значения брались из советующих строк столбца B.

СЧЁТЕСЛИ - Подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию.

Пример. В ячейке С5 необходимо сосчитать количество значений, не равных 2, в диапазоне А1:C3.

При вызове диалогового окна функции заполняем выбор диапазона и записываем условие.

Получаем в ячейке формулу =СЧЁТЕСЛИ(A1:C3;"<>2"), и результат равный 8. В данном случае пустые ячейки учитываются, так как значение в них не равно двум.

Логические функции

И – логические умножение,

возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА.

ИЛИ – логические сложение,

возвращает значение ИСТИНА, если хотя бы один аргумент имеет значение ИСТИНА.

НЕ – логическое отрицание,

меняет логическое значение своего аргумента на противоположное

Стоит отметить, что для логических функций в качестве значения ИСТИНА может выступать как непосредственно запись «ИСТИНА» в ячейке, так и любое число, не равное 0. Для значения «ЛОЖЬ» также может использоваться само слово и 0.

Пример. В ячейку C3 записали формулу =И(A1:C1). Полученное значение – ИСТИНА, так как в диапазоне только положительный числа

ЕСЛИ – выполняет проверку условия и присваивает ячейке определённое значение в случае выполнения и/или невыполнения условия.

Для её использования обязательно надо указать логическое выражение для проверки условия. Если не заполнить поля Значение_если_истина, Значение_если_ложно, то в случае истинности функция вернёт значение ИСТИНА, в случае ложности - ЛОЖЬ.

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

Построение графиков и диаграмм

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

В этом поле каждая иконка обозначает различные типы диаграмм:

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

График или диаграмма с областями служат для отображения показателей по времени или категориям.

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

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

Статистическая диаграмма используется для статистического анализа.

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

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

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

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

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

Использование фильтров

Табличные редакторы удобны для создания баз данных, основное назначение которых – поиск необходимой информации по какому-либо запросу. В Excel запросы реализуются с помощью фильтров. Excel располагает двумя фильтрами: Автофильтр и Расширенный фильтр, которые находятся в секции меню Данные/Фильтр.

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

Включим фильтр через меню Данные/Фильтр и найдем отличников по предмету Физика:

В итоге получаем следующий результат:

Интернет-энциклопедия по школьным предметам от Maximum Education. Учебник поможет решить домашнее задание, подготовиться к контрольной и вспомнить прошлые темы.

Технологии табличных вычислений