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

Символы подстановки (звездочка — любой текст любой длины; знак вопроса — один любой символ) используются в 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 заголовки столбцов превратятся из латинских букв в числа.

Если вам прислали книгу Excel и в ней используется неудобный вам стиль ссылок (например, вы видите числа в качестве номеров столбцов, а привыкли к стилю A1) — просто поменяйте его в «Параметрах».

В Google Таблицах нет стиля ссылок R1C1.

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

Одна часть этой формулы — ссылка на ячейку слева, на ячейку в столбце B в строке с формулой. Такая ссылка называется относительной. Она выглядит как B2, B3 и так далее.

Вторая часть — $F$1 — абсолютная ссылка на адрес F1 (ставку роялти 8%).

А так эта же формула выглядит со стилем R1C1.

Здесь обе ссылки выглядят одинаково во всех строках:

=RC[-1]*R1C6

Относительная ссылка — та, которая была разной в каждой строке при стиле A1, — здесь везде выглядит одинаково. И тут хорошо отражена ее суть, ведь RC[-1] — это ссылка на ячейку в той же строке (R без квадратных скобок) в столбце левее (-1 после буквы C).

А абсолютная ссылка выглядит так: R — номер строки, C — номер столбца. В нашем примере R1C6 — первая строка, шестой столбец, или ячейка F1 (при стиле ссылок A1).

ОБЪЕДИНЕНИЕ КНИГ EXCEL (ССЫЛКИ НА ДРУГИЕ КНИГИ В ФОРМУЛАХ)

В Excel можно ссылаться на другие книги в формулах (создавать связи).

Эти ссылки выглядят по-разному в зависимости от того, открыт источник (исходная книга) или нет.

Ссылка на другую (открытую в настоящий момент) книгу Excel выглядит так:

'[Имя_книги]Название_листа'!Диапазон

Например:

Ссылка на другую (закрытую в настоящий момент) книгу Excel: