В ячейках могут быть записаны данные различных типов: числа, строки, логические значения, даты и другие. Для изменения типа данных или для настройки параметров отображения данных используется меню Формат — Ячейки.
Обычно тип данных распознаётся автоматические. Но если хочется ввести несколько цифр в ячейку, чтобы типом ячейки была строка, нужно в начале записать один символ одинарной кавычки. Это нужно сделать также если хочется, чтобы в ячейке была строка, начинающаяся с символа “=”, то есть чтобы введённый текст не интерпретировался, как формула.
Если в начале содержимого ячейки ввести символ “=”, то содержимое ячейки считается формулой. В формулах могут использоваться адреса других ячеек, константы, функции, операции. В русскоязычном приложении в числовых константах для отделения дробной части используется запятая. Текстовые строки в формулах нужно заключать в двойные кавычки.
В формулах могут использоваться арифметические операции +, -, *, / (действительное деление), ^ (возведение в степень). Для целочисленного деления и вычисления остатка от деления используются функции QUOTIENT и MOD. Для конкатенации строк используется оператор &.
В формулах могут использоваться адреса других отдельных ячеек или блоков ячеек. Адрес ячейки имеет вид A1, где сначала записывается номер столбца, затем номер строки. Столбцы обозначаются буквами от A до Z, затем идут столбцы, обозначенные двумя буквами от AA до ZZ, затем столбцы из трёх букв от AAA и далее.
При вставке и удалении строк и столбцов в таблицу все адреса автоматически изменяются, то есть не нужно исправлять адреса при изменении таблицы.
Рассмотрим следующую таблицу:
В ячейке С1 записана формула =A1+B1. Если эту формулу скопировать в ячейку C2, то адреса в этой формуле автоматически изменятся на =A2+B2, а если скопировать ещё на одну клетку ниже, то формула примет вид =A3+B3. То есть адреса ячеек автоматически меняются, при копировании формулы на одну строку ниже, номера строк в формуле также уменьшаются на 1. Если же эту формулу из ячейки C1 скопировать в D1, то изменятся номера столбцов: =B1+C1. Говорят, что адреса в формулах являются относительными, то есть при копировании формул используется расположение ячеек относительно той ячейки, где записана формула.
Это позволяет создавать формулы, обрабатывающие столбцы или строки таблицы, а потом быстро копировать их, распространяя на все ряды обрабатываемых данных.
Если нужно, чтобы адрес какой-то ячейки не менялся, то есть был бы абсолютным адресом, необходимо в формуле записать этот адрес, поставив перед номером строки и столбца знак “$”: $A$1. Тогда этот адрес при копировании будет всегда иметь вид $A$1.
Можно сделать абсолютным только номер строки или номер столбца, а вторую часть адреса сделать относительной, то есть зафиксировать при копировании номер строки или столбца. В этом случае знак “$” нужно поставить только один раз, перед номером строки или столбца.
В формулах могут использоваться функции, в одной ячейке можно использовать много функций, можно вызывать функции от функций.
Аргументами функций обычно являются числа, строки, адреса других ячеек или блоки.
Блок ячеек это прямоугольник, он задаётся двумя противоположными углами. Их адреса записываются через двоеточие. Например, A1:B3 mdash; это блок из 6 ячеек. Углы можно задавать в произвольном порядке, например, B1:A3 — это тот же блок. В блоках также используется абсолютная и относительная адресация.
Аргументы функций разделяются точкой с запятой.
Названия функций в русифицированном офисе могут записываться русскими словами.
POWER(a; b) (СТЕПЕНЬ) — возвращает \(a^b\).
SQRT(a) (КОРЕНЬ) — возвращает \(\sqrt{a}\).
QUOTIENT(a; b) (ЧАСТНОЕ) — возвращает целочисленное частное от деления a на b.
MOD(a; b) (ОСТАТ) — возвращает остаток от деления a на b.
ABS(x) (ABS) — возвращает модуль числа x.
MIN(x; y; ...) (МИН) — возвращает наименьшее из аргументов. Также можно использовать с одним аргументом, являющимся блоком.
MAX(x; y; ...) (МАКС) — возвращает наибольшее из аргументов. Также можно использовать с одним аргументом, являющимся блоком.
SUM(a; b; c; ...) (СУММ) — возвращает сумму аргументов, каждый аргумент может быть блоком, тогда берётся сумма блока. Можно просто вызвать от одного аргумента (блока).
COUNT(a; b; c; ...) (СЧЕТ) — возвращает количество чисел среди своих аргументов.
AVERAGE(a; b; c; ...) (СРЗНАЧ) — возвращает среднее значение чисел среди своих аргументов.
SUMPRODUCT(a; b; ...) (СУММПРОИЗВ) — возвращает сумму произведений элементов, которые берутся из блоков–аргументов. Все аргументы должны быть блоками одного размера. Перемножаются по одному элементу из каждого блока, стоящие на одной позиции (то есть если два аргумента, то перемножаются пары чисел, если три аргумента — тройки чисел), и суммируются полученные произведения.
TRUE() (ИСТИНА) — возвращает истину (логическое значение).
FALSE() (ЛОЖЬ) — возвращает ложь (логическое значение).
IF(a; b; c) (ЕСЛИ) — проверяет условие a. Если оно истинно, то функция возвращает значение b, иначе возвращает значение с.
AND(a; b; ...) (И) — возвращает конъюнкцию всех своих аргументов.
OR(a; b; ...) (ИЛИ) — возвращает дизъюнкцию всех своих аргументов.
IFS(a1; b1; a2; b2; ...) (ЕСЛИМН) — проверяет условие a1. Если оно истинно, то функция возвращает значение b1. Если оно ложно, то проверяется условие a2, и если оно истинно, то возвращается b2. Если и оно ложно, то переходится к следующей паре условия и значения. Функция возвращает первое значение, для которого условие истинно.
Следующие функции позволяют найти количество значений, суммы, среднее и т.д. в блоке, удовлетворяющих некоторым условиям
COUNTIF(Блок; Условие) (СЧЕТЕСЛИ) — подсчитывает количество значений в данном блоке, удовлетворяющих условию. В качестве условия может быть одно значение, например, COUNTIF(A1:A10; 4) подсчитывает количество значений, равных 4. Также в качестве условия может быть строка вида "<0", "<=0", ">0", ">=0", "<>0" (вместо 0 можно написать любое число), строка должна быть заключена в кавычки. Например, COUNTIF(A1:A10; ">0") подсчитывает количество положительных чисел в блоке.
SUMIF(Блок; Условие; Блок суммирования) (СУММЕСЛИ) — подсчитывает сумму значений, для которых верно условие. При этом условие проверяется для первого блока, а суммируются значения, из второго блока. Оба блока должны быть одинакового размера. Например, SUMIF(A1:A10; ">0" B1:B10) суммирует числа из блока B1:B10 в тех строках, в которых в столбце A записаны положительные числа.
AVERAGEIF(СРЗНАЧЕСЛИ) — аналогично SUMIF, но вычисляет среднее значение.
COUNTIFS(Блок1; Условие1; Блок2; Условие2; ...) (СЧЕТЕСЛИМН) — возвращает количество записей, удовлетворяющих одному или нескольким условиям. Для каждого блока проверяется своё условие, все блоки должны быть одинакового размера. Например, COUNTIFS(A1:A10; ">0"; B1:B10; 5) возвращает количество строк, для которых в столбце A записано положительное число, а в столбце B записано 5.
SUMIFS(Блок суммирования; Блок1; Условие1; Блок2; Условие2; ...) (СУММЕСЛИМН) — возвращает сумму значений, удовлетворяющих одному или нескольким условиям. Значения берутся из блока в первом параметре. Обратите внимание, что порядок аргументов отличается от функции SUMIF.
AVERAGEIFS (СРЗНАЧЕСЛИМН), MINIFS (МИНЕСЛИ), MAXIFS (МАКСЕСЛИ) — аналогично SUMIFS возвращают среднее значение, наибольшее и наименьшее из записей, удовлетворяющих одному или нескольким условиям.
В электронной таблице может быть несколько листов, переключаться между которыми можно, кликая на вкладки с названиями листа внизу окна. По умолчанию листы называются Лист1, Лист2 и т.д., но их можно переименовать по своему усмотрению.
В формулах можно использовать ссылки на ячейки и блоки с других листов. Пример ссылки на ячейку A1 с листа Лист1: Лист1.A1. Можно ссылаться также на блоки, использовать в блоках абсолютную и относительную адресацию. Например, SUM(Лист1.$A$1:$A10).
Если в названии листа есть пробелы (или точка, или другие опасные символы), название листа заключается в одинарные кавычки: 'Результаты олимпиады'.A1.
В Microsoft Excel и в Google docs вместо точки используется восклицательный знак: Лист1!A1.
В электронных таблицах бывают сортировки и фильтры.
Сортировать можно листы целиком или отдельные блоки, для этого сначала нужно выделить блок. Сортировка находится в меню Данные — Сортировка.
В окне диалога сортировки можно указать один или несколько параметров сортировки (сортировка проводится по первому параметру, при равных значениях — по второму и т.д.). Сортировка является устойчивой. На вкладке “Параметры” диалога сортировки полезная опция Диапазон содержит метки столбцов.
Фильтры позволяют скрыть (временно) строки, соответствующие определённому условию.
Функция VLOOKUP позволяет искать записи (строки), в которых в определённых столбцах записано определённое значение. Использование:
VLOOKUP(Значение; Блок; Номер столбца; Признак сортировки) (ВПР)
Значение — число или строка, которую нужно найти.
Блок — обычно из нескольких столбцов. Поиск осуществляется по значениями из первого столбца блока.
Номер столбца — число (1, 2, 3...), не большее, чем число столбцов в блоке. Функция возвращает значение, которое находится в блоке в найденной строке (в первом столбце этого блока в этой строке записано то, что ищется), в столбце с данным номером.
Признак сортировки — почти всегда нужно указать 0. Значение 1 или TRUE(), а также положительное значение предполагает, что строки упорядочены по первому столбцу, тогда используется двоичный поиск.
Пример: VLOOKUP("Иванов"; Лист1.$C$100:$G$100; 3; 0). Осуществляется поиск записей в строках 1-100 на Лист1, у которых в столбце C записана строка "Иванов". Если строка будет найдена, то будет возвращено значение из столбца E (столбец C имеет номер 1, D — 2; E — 3).
Возможность поиска записей, удовлетворяющих нескольким критериям, отсутствует. В этом случае необходимо объединить значения из несколько ячеек вместе при помощи конкатенации.
Следующие функции позволяют проверять наличие ошибок при вычислении формул, например, если не удаётся найти значение при помощи функции VLOOKUP.
ISERROR(Выражение) — возвращает TRUE, если выражение является ошибочным, или FALSE при отсутствии ошибок.
IFERROR(Выражение; Другое выражение) — возвращает Выражение, если при его вычислении не произошло ошибок. Если же произошла ошибка, то возвращается Другое выражение.
IFNA — то же самое, но учитывается только ошибка #N/A.