Электронные таблицы. Основные принципы работы

Форматы

Файлы электронных таблиц имеют следующие расширения:

• xlsx - Office Open XML открытый формат. Является zip-архивом
• .xls - проприетарные документы MS Excel

Вид электронной таблицы

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

При использовании альтернативного вида ссылок на ячейки R1C1:

Включение вида R1C1 ссылок для Excel 2007: Параметры Excel → Формулы → Работа с формулами → Стиль ссылок R1C1

Ячейка

Ячейка является основным элементом электронных таблиц.

В ячейке может храниться:

• текст (набор символов)
• число (целое, дробное, процент, дата/время)
• формула (выражение, начинающееся с =, например, =1+2)
• логическое значение (ИСТИНА/ЛОЖЬ или TRUE/FALSE)
• пустое значение (ячейка не заполнена)
• ошибка (например, #ДЕЛ/0!, #Н/Д)
• гиперссылка
• мини-диаграмма
• изображение

Математические вычисления

Электронные таблицы позволяют проводить разные вычисления.

Рассмотрим пример арифметической (математической) операции:

Если подставить числа, то операция принимает вид:

для вычисления результата такой операции в электронных таблицах используется формула. Запись формулы начинается со знака = (равно):

Примечание: можно начать запись формулы с оператора, тогда знак = будет подставлен автоматически. Например запись -3+5 автоматически превратится в формулу =-3+5

Основные арифметические операции представлены в таблице:

Таблица арифметических операций

Если в операции больше одного оператора, то такая операция называется смешанная операция или комбинированная операция.

Пример комбинированной операции:

В соответствии с таблицей приоритет умножения 2, приоритет сложения 3, значит сначала будет произведена операция умножения, а затем операция сложения.

Если мы хотим произвести операцию с меньшим приоритетом до операции с большим приоритетом, то необходимо использовать скобки:

пример такой операции:

=(1+2)*3

результатом будет 9.

Ещё один пример:

=9^(1/2)

результатом будет 3, так как сначала будет вычислено деление в степени, равное 0.5, а далее 3 будет возведено в степень 0.5. Если скобки не поставить, то формула

=9^1/2

будет равна 4.5, т.к. сначала будет посчитано возведение в степень 9^1, а далее 9/2=4.5

Самостоятельное задание

В новом документе электронных таблиц посчитайте операции из таблицы выше.

Ссылки

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

Пример использования ссылки:

Самостоятельное задание

Столбцы имеют номера: A, B, C, …, X, Y, Z, …

Напишите следующие 5 номеров столбоцов после Z.

Самостоятельное задание

В режиме R1C1 число после R обозначает номер строки (row), число после C - номер столбца (column). Например ссылка A3 будет указывать на ту же ячейку, что и R3C1, аналогично для D5 и R5C4.

Заполните пропуски в таблице:

Протягивание ячеек

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

Для того, чтобы протянуть ячейку (или диапазон ячеек), нужно:

1. выделить ячейки, навести курсор на правый нижний угол ячейки (или диапазона),
2. зажать ЛКМ (левую клавишу мыши)
3. сместить курсор в сторону (вниз, вверх, влево или вправо), в которую необходимо добавить значения

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

Абсолютные и относительные ссылки

Указание строки и(или) столбца может быть относительным или абсолютным

• Относительная ссылка указывает на строку/столбец относительно той ячейки, в которой она записана и изменяется при копировании/переносе/протягивании ячейки
• Ссылки по-умолчанию - относительные
• Относительное положение в обычном виде ссылок определяется неявно
• При виде R1C1 относительные номера строк/столбцов указываются явно, положительные - сдвиг вниз/вправо, отрицательные - вверх/влево

Пример копирования относительных ссылок - вид ссылки меняется в зависимости от ячейки, куда она была скопирована:

• Абсолютная ссылка указывает на конкретную строку или столбец и не изменяется при копировании/переносе/протягивании ячейки.
• Для указания абсолютной ссылки на строку/столбец в обычном стиле, перед строкой/столбцом необходимо поставить знак $
• Абсолютные ссылки удобно использовать для определения постоянных величин (констант).

Пример копирования ссылки с абсолютным указанием столбца - строка не меняется при копировании:

• Для смены типа ссылки используется клавиша F4

Примеры разных типов ссылок:

Таблица относительных и абсолютных ссылок

Самостоятельное задание

Заполните пустые ячейки таблицы:

Имена

Любой ячейке или диапазону ячеек можно присвоить Имя. Имя представляет собой абсолютную ссылку:

Создавать, изменять, удалять имена можно с помощью инструмента Формулы - Диспетчер имён:

Правила создания имён:

• Первым символом имени должна быть буква, знак подчеркивания (_) или обратная косая черта (\). Остальные символы имени могут быть буквами, цифрами, точками и знаками подчеркивания.
• Имена не могут быть такими же, как ссылки на ячейки, например, Z$100 или R1C1.
• Нельзя использовать имена ячеек: R, r, C, c, так как эти буквы используются для ссылок на ячейки.
• Пробелы не допускаются. В качестве разделителей слов используйте символ подчеркивания (_) или точку (.), например, Налог_Продаж или Первый.Квартал.
• Имя может содержать до 255-ти символов.
• Имя может состоять из строчных и прописных букв. При этом процессоры электронных таблиц не различают строчные и прописные буквы в именах.

Зависимости формул

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

Можно включить визуальное отображение зависимостей между ячейками с помощью раздела Формулы - Зависимости формул:

Пример включения опции Влияющие ячейки:

Полный путь ссылки

До это мы рассматривали ссылки, которые работали в пределах одного листа. Если мы хотим сослаться на ячейки на другом листе документа, мы должны добавить имя листа перед ссылкой:

В примере выше написаны два варианта ссылки, с кавычками и без. Можно писать и так, и так. Но если в названии листа есть пробелы, то использовать кавычки обязательно.

Если мы хотим сослаться не только на другой лист текущего документа, но на лист другого документа (который открыт в данный момент), то нужно перед листом ещё указать имя документа (называемый книгой):

тогда полная ссылка будет выглядеть так:

=`[Книга2.xlsx]Лист1`!A1

Ссылка на диапазон ячеек

Для ссылки на диапазон ячеек нужно указать левую вверхнюю и правую нижнюю ячейку, разделённых знаком двоеточие (:), как показано на примере:

Самостоятельное задание

Напишите ссылку на диапазон ячеек, в который вместятся все цифры шестнадцатиричной системы счисления.

Форматы данных

Каждой ячейке может быть назначен формат данных. В зависимости от формата данные будут записываться и отображаться в разном виде.

• Если формат не указывать явно, то он будет назначен при вводе значений в ячейку.
• Формат можно назначить сразу диапазону ячеек. В том числе можно назначить формат всему столбцу или всей строке.

Указание формата ячейки:

Оформление таблиц

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

Инструменты оформления электронных таблиц соответствуют оформлению таблиц в текстовых документах:

• Шрифт, размер шрифта, начертание, цвет, выравнивание, направление текста, перенос текста
• Заливка и границы ячейки

Функции

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

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

Мастер функций

Выбор функции:

Ввод аргумента функции:

Ввод аргументов функции для функции нескольких аргументов:

Самостоятельное задание

Откройте мастер функций и найдите, в какой категории расположены указанные функции. Заполните таблицу:

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

Самостоятельное задание

Для каждого математического выражения запиши формулу электронной таблицы:

Глоссарий

Добавьте слова и фразы в свой глоссарий