Текст книги "Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)"

Проверка данных (Data Validation) с формулами

Для понимания этого раздела стоит разобраться с логическими значениями и формулами. Если вы еще не знакомы с этой темой, обратитесь к главе «Логические выражения и функция ЕСЛИ / IF».

Файл с примерами: 3 Проверка данных с формулами. xlsx

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

Для создания правила проверки с формулой нужно проследовать по следующему пути:

Данные → Проверка данных → Тип данных: Другой → Формула (Data → Data Validation → Allow: Custom → Formula).

Формула должна возвращать ИСТИНА (TRUE), то есть условие должно выполняться. Иначе проверка данных будет выдавать ошибку или предупреждение – зависит от настроек в разделе «Сообщение об ошибке» (Error Alert).

Рассмотрим несколько примеров проверки с формулами.

Разрешаем вводить текст только меньше определенной длины

Длину текста можно определить с помощью функции ДЛСТР / LEN. Она возвращает число – количество символов в ячейке. Соответственно, если мы хотим запретить ввод текста длиннее, допустим, десяти символов, то формула будет выглядеть следующим образом:

=ДЛСТР(ссылка на первую ячейку диапазона) <= 10

Разрешаем вводить только формулы

Функция ЕФОРМУЛА / ISFORMULA возвращает ИСТИНА, если ее аргумент – ячейка с формулой. Соответственно, следующая формула в проверке данных будет разрешать ввод только формул.

=ЕФОРМУЛА(ссылка на первую ячейку диапазона)

Разрешаем вводить только уникальные значения

Уникальные значения – это значения, которые в диапазоне встречаются всего лишь один раз. Подсчитать количество можно с помощью СЧЁТЕСЛИ / COUNTIF:

=СЧЁТЕСЛИ(диапазон; критерий)

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

=СЧЁТЕСЛИ(проверяемый диапазон; первая ячейка этого диапазона)

И сравнивать это с единицей: если значение будет встречаться более одного раза, его вводить уже нельзя.

=СЧЁТЕСЛИ(проверяемый диапазон; первая ячейка этого диапазона)<=1

Если нам нужно запретить ввод повторяющихся значений в диапазоне A2:A30, то формула будет выглядеть так:

=СЧЁТЕСЛИ(A$2:A$30; A2)<=1

Сортировка данных

Сортировка данных – упорядочивание по значениям в одном или нескольких столбцах. Без этой операции никуда: а как иначе посмотреть топ продаж по какому-либо параметру (городам, товарам и т. д.)? Это помогает упорядочить таблицу, анализировать данные становится проще.

Быстрее всего отсортировать данные по возрастанию или убыванию можно на вкладке «Данные».

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

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

Кроме того, сортировать можно с помощью фильтра (о нем ниже).

А если простой сортировки по одному столбцу недостаточно, открывайте окно «Сортировка» (одноименная кнопка на ленте).

Здесь есть возможность сортировать по нескольким столбцам. Допустим, мы хотим упорядочить таблицу по клиентам, а в рамках каждого клиента – по сумме, чтобы самые крупные сделки были сверху. Тогда мы добавляем два соответствующих уровня сортировки в этом окне – сначала компанию, а затем, нажав кнопку «Добавить уровень» (Add Level), – сумму.

Результат:

В «Параметрах» в сортировке есть две опции: можно сортировать не строки, а столбцы и учитывать регистр текста при сортировке.

Сортировка с помощью пользовательских списков

Не всегда нужно сортировать данные по алфавиту.

Возможно, вы уже обращали внимание, что месяцы или дни недели сортируются не по алфавиту, а в правильном (хронологическом) порядке. Это происходит потому, что в Excel встроены соответствующие списки для сортировки – с короткими и длинными названиями месяцев и дней недели. Мы не можем их удалить или изменить, но можем добавлять новые!

Допустим, в вашей компании есть правила сортировки списка филиалов – не по алфавиту. В таком случае стоит создать пользовательский список.

Списки добавляются и удаляются в параметрах Excel → Дополнительно → Общие (раздел в самом конце вкладки «Дополнительно») → Изменить списки (Options → Advanced → General → Edit Custom Lists).

В окне со списками есть списки дней недели и месяцев – их редактировать и удалять нельзя. Но можно добавлять свои.

После добавления сортировка в диапазонах, таблицах и сводных таблицах может осуществляться по такому порядку. Кроме того, список можно создать непосредственно в процессе сортировки. В любом случае нужно выбрать «Настраиваемый список…» (Custom List…) в окне сортировки.

И далее в открывшемся окне выбрать список (или добавить новый).

Еще один плюс пользовательских списков: они работают при протягивании.

В Google Таблицах пользовательских списков нет.

Фильтрация данных

Фильтрация данных – еще одна базовая операция, без которой никуда в Excel или Google Таблицах. Фильтрация – отбор и отображение строк по заданным критериям (текстовым, числовым или даже по цвету ячейки/шрифта).

Фильтр можно установить и снять сочетанием клавиш Ctrl + Shift + L или на ленте инструментов на вкладке «Данные» (Data → Filter).

Кроме того, если вы превращаете диапазон в таблицу – сочетания клавиш Ctrl + L или Ctrl + T, «Вставка → Таблицы» (Insert → Table) или «Главная → Форматировать как таблицу» (Home → Format as Table) на ленте, фильтр создается автоматически (подробнее об этом инструменте мы поговорим в соответствующей главе ниже).

О наличии фильтра на листе нам сигнализируют кнопка «Фильтр» и собственно кнопки фильтра в ячейках с заголовками.

ВЫБОР ОПРЕДЕЛЕННЫХ ЗНАЧЕНИЙ

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

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

Отображаются строки с двумя продуктами из трех – курсами и электронными библиотеками

Чтобы снять фильтрацию в определенном столбце, нажмите «Снять фильтрацию с…» (Clear Filter From) или «Выделить все» (Select All), что сделает все элементы выделенными, а это значит, что фильтрации по столбцу не будет.

Чтобы снять фильтрацию во всех столбцах быстро, проще всего снова нажать Ctrl + Shift + L.

ТЕКСТОВЫЕ ФИЛЬТРЫ

Допустим, нам нужны все сделки с индивидуальными предпринимателями или ООО. Конечно, в нашем случае можно выбрать их и по одному, но что, если клиентов (как это и будет в реальной жизни) намного больше?

Тогда лучше воспользоваться текстовыми фильтрами: в открывающемся списке будет несколько вариантов.

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

В фильтре можно использовать символы подстановки: например, если вы хотите фильтровать и ООО, и ОАО, можно поставить знак вопроса вместо второй буквы. Тогда будут фильтроваться все строки с буквами О на первой и третьей позиции и любым символом между ними.

Поэтому, если вам нужны строго два варианта, лучше выбрать «ИЛИ» (Or) и указать их без символов подстановки.

ЧИСЛОВЫЕ ФИЛЬТРЫ

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

Например, можно отобразить строки со сделками крупнее 12 000.

Обратите внимание на числовой фильтр «Первые 10» (Top 10). По умолчанию это 10 наибольших значений.

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

Кроме того, можно наложить условие не по количеству элементов, а по процентам. Например, посмотреть на 20 % самых крупных сделок.

ФИЛЬТР ПО ЦВЕТУ

Фильтровать можно по цвету (заливки и шрифта), и неважно, был этот цвет изменен вами вручную или он появляется в результате работы условного форматирования.

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

Здесь используется формула для форматирования ячеек =ДЕНЬНЕД(D2;2)>5 – мы проверяем, что день недели больше 5-го (то есть 6 или 7). Второй аргумент функции ДЕНЬНЕД определяет тип нумерации; 2 – начало недели с понедельника, то есть ему соответствует единица.

Мы можем отфильтровать (и отсортировать тоже) эти строки по цвету.

В Google Таблицах фильтр устанавливается на панели инструментов или через меню «Данные» (Alt + D → F).

Чтобы настраивать фильтры, которые не будут видны другим пользователям при совместной работе (с книгами Excel, хранящимися в облаке), используйте «Представления» (Sheet Views) – о них подробнее в соответствующей главе ниже.

ОБНОВЛЕНИЕ РЕЗУЛЬТАТОВ ФИЛЬТРАЦИИ

Результаты фильтрации не обновляются сами. Если вы поменяли данные и какая-то строка уже не должна отображаться при текущих критериях в фильтре, она все равно будет отображаться, пока вы не примените фильтр снова. Если добавились новые данные в конце диапазона, они тоже не отфильтруются автоматически.

Для обновления фильтра нажмите Ctrl + Alt + L. Или кнопку «Повторить» (Reapply) на ленте рядом с кнопкой фильтра.

Если у вас Excel 2021 / Microsoft 365 или вы используете Google Таблицы, можно использовать функции СОРТ / SORT и ФИЛЬТР / FILTER. Так как это функции, то результат (отсортированные и/или отфильтрованные данные) будет обновляться автоматически при изменении исходных данных.

ФИЛЬТРЫ В GOOGLE ТАБЛИЦАХ

Обычный фильтр и результаты фильтрации в Google Таблицах видны пользователям с любым доступом. Если вы отфильтровали данные с его помощью, то другие люди не увидят скрытые строки, пока не снимут фильтр (снять его они смогут, только имея доступ на редактирование).

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

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

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

Именно режимы фильтрации – оптимальное решение для совместной работы. Обычный фильтр лучше вообще не использовать, если пользователей больше одного. При этом не забывайте, что фильтры сохраняются. По умолчанию – под названиями вида ФильтрN, где N – порядковый номер.

Хорошее правило – периодически удалять все фильтры без названий, чтобы не засорять файл. Если у фильтра нет названия, значит, его создали «на один раз». Правильная же ситуация – когда у каждого отдела/сотрудника/ситуации есть свой фильтр, например: «Иванов», «Маркетинг» или «Проекты в работе с приоритетом 2».

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

Подробнее о фильтрах в Google (и аналоге в Microsoft 365) – в главе «Совместная работа».

Вычисления в Excel: функции и формулы

Что такое формула и функция в Excel

Формула Excel (как и Google Таблиц) – это вычисление в ячейке. В отличие от значения, введенного в ячейку вручную (текста, числа или даты, вставленных как значения или введенных с клавиатуры), формула вычисляется и возвращает результат, который пересчитывается, если изменяются ячейки, которые в этой формуле используются (на которые она ссылается).

Автоматический пересчет формул можно отключить – например, если их в книге очень много и вам не хочется после любого действия ждать, пока большое количество формул обновится. Это можно сделать в параметрах Excel: Параметры → Формулы → Вычисления в книге → Вручную (Options → Formulas → Workbook Calculation → Manual).

А также на ленте во вкладке «Формулы».

Там же справа есть кнопки для пересчета всех листов (F9, Fn + F9) или текущего листа (Shift + F9, Fn + ⇑ + F9).

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

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

Кроме того, можно включить режим отображения формул: вкладка «Формулы» – «Показать формулы» (Show Formulas), тогда вы будете видеть их прямо в ячейках.

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

Ввод формулы начинается со знака «равно» (=). Если вы хотите ввести текст, который начинается с этого знака, введите перед ним апостроф (') – он автоматически превращает значение в ячейке в текст, и оно не будет вычисляться как формула.

В формулах используются:

• знаки математических операций: плюс (+), минус (-), деление (/), умножение (*), возведение в степень (^);

• знак «амперсанд» (&) – он используется для «склеивания» (объединения, конкатенации) текстовых значений;

• круглые скобки для определения порядка операций;

• числа и текст, введенные вручную прямо в формуле (текст указывается в формулах в кавычках);

• ссылки на ячейки и диапазоны (они указываются без кавычек);

• пробел: это оператор пересечения (например, A1:E5 B2:D6 – это ссылка на пересечение двух диапазонов (на их общие ячейки));

• решетка (#): это ссылка на массив, который возвращается формулой из указанной ячейки (такой тип ссылок работает только в Excel 2021 / Microsoft 365); например, A1# – это ссылка на массив значений, который возвращает формула, находящаяся в ячейке A1 (мы подробно обсудим в главе «Динамические массивы», зачем нужны и как работают такие ссылки);

• знаки сравнения: «равно» (=) (знак «равно» в начале формулы присутствует всегда, а еще один может использоваться, если мы хотим сравнить два значения), «больше» (>), «меньше» (<), «больше либо равно» (>=), «меньше либо равно» (<=), «не равно» (<>);

• функции рабочего листа Excel (например, СУММ / SUM, СРЗНАЧ / AVERAGE, ВПР / VLOOKUP и другие, всего функций в Excel более 500 – количество разнится в зависимости от версии); ниже мы поговорим о том, как вставлять функции в формулы.

Ссылки на ячейки в формулах

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

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

• Ввести ее адрес с клавиатуры (не забывайте, что номера столбцов – это латинские буквы).

• Выделить соседнюю с формулой ячейку с помощью клавиш со стрелками (это удобно, если вам нужно сослаться на ячейку, которая находится недалеко от ячейки с формулой). Когда вы вводите формулу, есть два режима редактирования: в одном нажатие стрелок на клавиатуре перемещает курсор в самой формуле, во втором – выделяет ячейки. Переключаются эти режимы клавишей F2 (^ + U).

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

То же самое происходит не только при первом вводе формулы, но и если войти в режим редактирования (нажать F2 или щелкнуть двойным щелчком по ячейке с формулой или щелкнуть на строку формул).

АБСОЛЮТНЫЕ И ОТНОСИТЕЛЬНЫЕ ССЫЛКИ

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

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

Если мы ссылаемся из ячейки C2 на A2 – это ссылка на ячейку на два столбца левее от формулы, а не на ячейку с адресом A2.

Потому что стоит скопировать и вставить или протянуть (мышкой за правый нижний угол ячейки) такую формулу вниз в следующую строку – и ссылка на A2 превратится в A3. А если скопировать формулу вправо – то в B2. Это по-прежнему будет ссылка на ячейку на два столбца левее от формулы, потому она и называется относительной.

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

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

А вот если нам необходимо ссылаться на ячейку с определенным адресом, куда бы мы ни протягивали формулу (например, если для разных строк нам нужно брать одну и ту же ставку налога из ячейки D1), то нужно использовать абсолютные ссылки. Для этого достаточно нажать F4 ( + T) после того, как вы щелкнули на ячейку в формуле (или после того, как установили курсор на ссылку в формуле). Появятся доллары у номеров строки и столбца – это означает, что ссылка стала абсолютной, а не относительной. То есть ссылкой на ячейку с адресом D1, а не на ячейку справа и сверху от формулы.

Эту формулу следует читать так (с точки зрения ссылок): произведение ячейки слева от формулы (B3) и ячейки D1 ($D$1).

И после протягивания формулы вниз по всей таблице она по-прежнему будет содержать ссылки на ячейку слева от себя (в каждом случае это будут разные ячейки) и на ячейку D1.

Если после нажатия F4 и формирования абсолютной ссылки нажать эту клавишу еще один или два раза, то ссылка поменяется на так называемую смешанную: закрепится только номер строки или только номер столбца.

Ссылка вида A$1 – это ссылка на первую строку, а вот ссылка на столбец остается относительной, то есть будет меняться при копировании формулы с такой ссылкой. Аналогично $A1 – это ссылка на столбец A, а номер строки будет меняться при перемещении формулы.

В следующем примере нам нужно перемножить возможные варианты объема производства (из строки 2) и оптовых цен (из столбца B) – чтобы на пересечении получить сценарии по выручке для каждой пары параметров.

Относительные ссылки не подойдут: как только мы вставим формулу правее и ниже, то начнем перемножать не ячейки с параметрами, а предыдущие сценарии выручки.

Абсолютные ссылки тоже не подойдут: с ними мы в любых ячейках будем умножать одни и те же ячейки C2 и B3 друг на друга. Это пример ситуации, когда нужны смешанные ссылки, ведь нам нужно перемножать разные (относительная часть ссылки) числа из столбца B (абсолютная часть ссылки) с разными (относительная) числами из строки 2 (абсолютная).

ССЫЛКИ НА СТОЛБЦЫ И СТРОКИ ЦЕЛИКОМ

В Excel (и в Google Таблицах) можно ссылаться не только на отдельные ячейки и диапазоны, но и на столбцы и строки целиком.

Вот как выглядят такие ссылки:

2:2 – относительная ссылка на всю вторую строку;

$3:$3 – абсолютная ссылка на всю третью строку;

A: E – относительная ссылка на пять столбцов, от A до E, целиком;

$B:$B – абсолютная ссылка на весь столбец B.

Плюс в том, что все строки будут учитываться в формуле. Минус в том, что попадут лишние, то есть мы априори ссылаемся на миллион с лишним строк.

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

ИМЕНОВАННЫЕ ДИАПАЗОНЫ

Файл с примерами: Имена диапазонов. xlsx

В Excel и Google Таблицах диапазонам можно присваивать имена. После того как диапазону присвоено имя, на него можно ссылаться в формулах, и такие формулы читаются легче (=Налог*Выручка, а не =B2*$E$1). Кроме того, имена помогают в некоторых особых ситуациях.

В Excel можно задать имя следующими способами.

Выделить ячейку/диапазон и ввести имя в поле, которое так и называется – «Имя», находится оно слева от строки формул.

Если в диапазоне есть заголовки и вы хотите их использовать в качестве имен, используйте команду «Создать из выделенного» на вкладке «Формулы» (Formulas – Create from Selection). Можно также использовать сочетание клавиш Ctrl + Shift + F3.

Еще можно пользоваться диспетчером имен – это окно вызывается сочетанием Ctrl + F3, там можно и создавать имена, и просматривать/изменять/удалять существующие.

В Google Таблицах имена настраиваются в меню «Данные» – «Настроить именованные диапазоны» (Data – Named ranges). Можно задавать и в поле «Имя» слева от строки формул, как в Excel.

Есть некоторые ограничения:

• имя может быть длиной до 255 символов;

• имена могут начинаться с буквы, нижнего подчеркивания, обратной косой черты;

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

После того как мы задали имя, мы можем использовать его в формулах.

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

Если мы присвоили имя одной ячейке, можно ссылаться на нее (это будет словно абсолютная ссылка), не переживая, что ссылка «поедет» (как это было бы с относительной ссылкой).

Если же имя присвоено целому диапазону, то мы можем:

• ссылаться на значение из этого диапазона в той же строке/столбце (своего рода относительная ссылка) в версиях Excel до 2019 включительно или ссылаться сразу на весь диапазон в Excel 2021 / Microsoft 365;

• ссылаться на весь диапазон – например, вычислить сумму всех значений из именованного диапазона.

Имена могут действовать на уровне всей книги (то есть вы можете ссылаться на данные по имени с любого листа) – и это более частый вариант. Либо на уровне одного рабочего листа.

Область действия задается в одноименном поле в диалоговом окне «Вставка имени». Книга – это вся книга Excel, а далее идут названия листов.

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

Поле «Имя» можно использовать для быстрой навигации в книге: можно выбрать имя из списка. После выбора соответствующий диапазон будет выделен.

А еще можно просто ввести в этом поле адрес любой ячейки и нажать Enter, чтобы к ней перейти. Кстати, это пригодится, чтобы посмотреть, какие данные есть в скрытой ячейке (и даже отредактировать их).

Также перейти к любой ячейке можно с помощью окна «Переход» (вызывается клавишей F5).

Итак, в чем польза имен:

• формулы легче воспринимать;

• имена можно выбирать из выпадающего списка при вводе формул;

• быстрый переход к именованному диапазону через поле «Имя»;

• абсолютная ссылка на ячейку/диапазон без необходимости использовать знаки доллара.

Кроме того, они будут отображаться при уменьшении масштаба до того уровня, когда смогут поместиться поверх соответствующих диапазонов.

ССЫЛКА НА ПЕРЕСЕЧЕНИЕ ДИАПАЗОНОВ

Файл с примером: Пересечение диапазонов. xlsx

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

Например, если присвоить всем диапазонам в этой таблице имена (это можно сделать с помощью команды «Создать из выделенного» на вкладке ленты «Формулы»)…

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

=Материалы март

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

В Google Таблицах оператор пересечения диапазонов не работает.

ССЫЛКИ НА ДРУГИЕ ЛИСТЫ

Ссылка на другой лист в Excel и Google Таблицах выглядит так: название листа, восклицательный знак, ссылка на диапазон (ячейку).

'название листа'!ссылка на диапазон

Название листа отделяется от ячейки или диапазона восклицательным знаком. Название листа берется в апострофы:

'Лемур'!A1:B300 – это ссылка на диапазон A1:B300 на листе «Выручка».

'Продажи Лемур'!D7 – это ссылка на ячейку D7 на листе «Продажи Лемур».

Если название листа состоит из одного слова (если в нем нет пробелов, дефисов, плюсов – таких символов, которые могут быть частью формулы в Excel и интерпретироваться соответственно) – апострофы не обязательны.

То есть будут работать оба варианта:

'Лемур'!A1:B300

Лемур! A1:B300

А такой вариант ссылки работать не будет:

Продажи Лемур! D7

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

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

Также на таблицы (смотрите соответствующую главу «Таблицы Excel» ниже) можно ссылаться с других листов по имени таблицы и названию столбца без упоминания названия листа.

ССЫЛКА НА НЕСКОЛЬКО ЛИСТОВ В ФОРМУЛЕ

Файл с примером: Несколько листов. xlsx

А что, если нужно обработать данные с нескольких листов формулой? Например, сложить несколько ячеек с группы листов или рассчитать среднее значение?

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

Допустим, нам нужна сумма чисел из ячеек B2 на листах от «Москва» до «Казань». Формула получится такой:

=СУММ(Москва: Казань! B2)

Обратите внимание, что, если после ввода формулы порядок листов поменяется (вы переместите какие-то листы), формула не изменится: она по-прежнему будет суммировать данные с группы листов, первый (левый ярлык) из которых называется «Москва», а последний (самый правый ярлык) – «Казань».

То есть если между этими листами добавится новый, то данные с него тоже попадут в расчет. Если, наоборот, какой-то из листов окажется левее «Москвы» или «Казани», то есть покинет эту группу, данные с него не будут суммироваться.

P. S. Если в названии хотя бы одного из крайних листов есть пробелы/дефисы, то нужно будет взять оба названия в апострофы (как и в случае со ссылкой на один лист с этими символами):

=СУММ('Санкт-Петербург: Казань'!B2:B4)

ССЫЛКА НА ЛИСТЫ, У КОТОРЫХ В НАЗВАНИИ ЕСТЬ ОБЩИЕ ТЕКСТ/СЛОВО

Файл с примером: Ссылка на листы с символом подстановки. xlsx

Допустим, у вас в книге есть много листов с названиями «Доходы январь», «Доходы февраль», «Расходы январь», «Расходы февраль» или другие группы листов, в которых есть определенные слова. Например, с городами – по два листа (доходы и расходы) на каждый город.

И вам нужно обработать данные на всех листах, например, с расходами – во всех городах. Или с символами HR в названии. Или с определенным городом. И эти листы могут идти не по порядку.

Тогда можно использовать символ подстановки – звездочку: на месте этого символа может быть любой текст (в том числе нулевой длины, то есть ничего).

Символы подстановки (звездочка – любой текст любой длины; знак вопроса – один любой символ) используются в Excel не только для решения этой задачи – они работают в некоторых функциях и инструментах.

Символы подстановки работают по умолчанию в следующих функциях:

– ВПР / VLOOKUP и ПОИСКПОЗ / MATCH;

– СУММЕСЛИ (МН) / SUMIF(S), СЧЁТЕСЛИ(МН) / COUNTIF(S), СРЗНАЧЕСЛИ(МН) / AVERAGEIF(S);

– COUNTUNIQUEIFS;

– в функциях баз данных: БДСУММ / DSUM, ДСРЗНАЧ / DAVERAGE, БСЧЁТ / DCOUNT, БСЧЁТА / DCOUNTA и других;

– ПОИСК / SEARCH.

В новых функциях ПОИСКПОЗX / XMATCH и ПРОСМОТРX / XLOOKUP символы подстановки по умолчанию не работают! Но у них есть аргумент «режим_сопоставления» (match_mode), в котором имеются следующие варианты:

0 – точный поиск (по умолчанию), символы подстановки не работают;

1 – ближайшее большее значение (или точное совпадение);

– 1 – ближайшее меньшее значение (или точное совпадение);

2 – точный поиск с символами подстановки.

Вводим формулу с нужным словом и звездочкой – там, где может встречаться любой текст. Не забудьте про апострофы! После ввода формулы в ней появятся отдельные ссылки на все листы, в которых есть слово «Доходы». Обратите внимание, что если такие листы идут подряд, то будет ссылка на несколько листов сразу (в нашем примере 'Доходы Москва: Доходы Санкт-Петербург', а если отдельно – то будут отдельные ссылки ('Доходы Казань').

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

Это работает только в Excel.

СТИЛЬ ССЫЛОК R1C1

В Excel есть два стиля ссылок – описанный выше (и более распространенный) стиль A1 и стиль R1C1, в котором и столбцы, и строки обозначаются числами.

Изменить стиль ссылок можно в параметрах Excel:

Формулы – Стиль ссылок R1C1

(Formulas – R1C1 reference style).

С включенным стилем R1C1 заголовки столбцов превратятся из латинских букв в числа.