В отличие от Excel, в Google Таблицах нельзя ссылаться на другие файлы прямо в формулах и таким образом связывать их — мы можем сослаться только на другой лист в той же таблице. Для связывания таблиц используется функция IMPORTRANGE.
Для загрузки вам нужно иметь доступ к этому файлу — на редактирование или на комментирование/просмотр (без запрета на скачивание и копирование данных: если он включен, то не получится импортировать данные и с помощью IMPORTRANGE).
У функции два аргумента — ссылка на таблицу и ссылка на диапазон.
Ссылка на таблицу может указываться в двух видах — полная (со ссылкой на лист или без нее — не имеет значения, лист в любом случае определяется вторым аргументом функции) или ключ (набор символов после общей части ссылки docs.google.com/spreadsheets/d/).
=IMPORTRANGE(ссылка на файл; ссылка на лист и диапазон)
Пример нескольких ссылок на файл, которые будут работать одинаково в функции IMPORTRANGE (первый аргумент):
• https://docs.google.com/spreadsheets/d/1wWrgdcpIPeS3THHjZzkcPGMqwFCDHSTVlE4j1G6Dppc/edit#gid=1556931255 (полная ссылка с указанием номера листа);
• https://docs.google.com/spreadsheets/d/1wWrgdcpIPeS3THHjZzkcPGMqwFCDHSTVlE4j1G6Dppc (полная ссылка, но без листа; повторимся: в любом случае лист будет указываться отдельно во втором аргументе, в ссылке его отсутствие или присутствие ни на что не влияет);
• 1wWrgdcpIPeS3THHjZzkcPGMqwFCDHSTVlE4j1G6Dppc (только ключ).
Второй аргумент — диапазон — может задаваться несколькими способами:
• без указания названия листа (например, "B2:E"; в таком случае данные будут тянуться из диапазона B2:E с первого по порядку листа в исходном документе);
• с указанием названия листа: "Продажи! A2:D" или "Продажи! A1:L20";
• с использованием имени диапазона, если в исходном файле есть таковые. Например, "Налог".
И первый, и второй аргумент могут задаваться как в кавычках прямо в функции, так и в ячейках, на которые вы будете ссылаться из функции.
Обратите внимание на следующие нюансы.
Функция IMPORTRANGE выводит массив данных, а не одну ячейку (в частном случае она может возвращать и одну ячейку, если вы указали такой диапазон во втором аргументе, но в большинстве случаев она все же используется для загрузки таблиц, а не одиночных ячеек). Это значит, что справа и снизу от него должно быть достаточно пустых ячеек для вывода этих данных. Если в ячейках есть данные, то функция не сможет их «перезаписать» выводимым массивом и вернет ошибку.
IMPORTRANGE возвращает только значения и числовые форматы, но не переносит стилевое форматирование — заливку, шрифт и так далее. Форматирование нужно настраивать отдельно в каждой таблице.
Не обязательно сначала выводить данные из другого файла, а потом их обрабатывать — их можно обработать, используя IMPORTRANGE как аргумент другой функции: например, сразу получить среднее из диапазона в другом файле с помощью СРЗНАЧ, аргументом которой будет IMPORTRANGE. Тогда не нужно будет выводить сами данные в конечной таблице.
Функции рабочего листа Excel
В формулах Excel используются функции. Функции принимают на входе аргументы (в подавляющем большинстве случаев; есть несколько функций без аргументов, об этом ниже) — параметры, ссылки на ячейки и диапазоны — и возвращают на выходе некий результат в соответствии со своим назначением. Например, одна из самых простых и известных функций СУММ / SUM возвращает сумму всех своих аргументов (чисел, введенных вручную, чисел из отдельных ячеек и из диапазонов). В Excel более 500 функций — есть специализированные, используемые редко, есть актуальные для большинства пользователей.
Следующая функция будет возвращать сумму чисел из ячейки B5, диапазона A1:B2 и числа 1200:
=СУММ(B5; 1200; A1:B2)
А следующая функция будет возвращать среднее значение чисел из диапазона B2:B12:
=СРЗНАЧ(B2:B12)
СУММ / SUM и СРЗНАЧ / AVERAGE — пример функций с переменным количеством аргументов (обязательно требуется только один аргумент, чтобы было что суммировать или усреднять, но может быть и больше).
Бывают функции вообще без аргументов: например, ТДАТА / NOW — она возвращает текущие дату и время, а они ни от чего не зависят, у них не может быть параметров. Поэтому у таких функций скобки остаются пустыми ввиду отсутствия аргументов:
=ТДАТА()
Скобки в любом случае нужно указать, чтобы Excel понимал, что ТДАТА — это функция (а не имя диапазона, например).
Автосумма
Сочетание клавиш Alt + = позволяет получить сумму быстро, не вводя руками функцию СУММ / SUM.
Если выделить ячейку под столбцом с числами и нажать Alt + =, то получим сумму по этому столбцу (одну функцию СУММ / SUM).
А если выделить диапазон из нескольких столбцов и строк вместе с пустой строкой под ним и столбцом справа, то получим суммы по каждому столбцу и строке (и итоговую справа внизу).
После нажатия Alt + = получаем суммы во всех пустых ячейках выделенного диапазона.
Вместо сочетания клавиш можно нажать кнопку на ленте инструментов на вкладке «Формулы» (Formulas).
Здесь доступны еще несколько базовых функций: среднее арифметическое (СРЗНАЧ / AVERAGE), счетчик (количество чисел; функция СЧЁТ / COUNT), максимум (МАКС / MAX), минимум (МИН / MIN). Чтобы открыть этот список, нужно щелкнуть на стрелку справа от слова «Автосумма» (AutoSum).
Инструменты для ввода функций
В автосумме доступно лишь несколько базовых функций. Что, если мы хотим вставить другую функцию? Здесь есть несколько вариантов.
Можно начать вводить название функции — появится всплывающая подсказка с теми функциями, которые начинаются на введенные буквы, а далее можно выбрать нужную (щелкнуть мышкой дважды или выделить функцию стрелками на клавиатуре и нажать Tab).
Конечно, можно ввести название функции целиком вручную. Тогда нужно будет открыть скобку. А вот в случае нажатия Tab или щелчка скобка будет открыта автоматически.
Аналогично в Google Таблицах (что в них, что в Excel подсказки работают на том языке интерфейса, который включен в настройках).
При этом в Google Таблицах можно ввести название функции на другом языке, и она все равно будет работать (если вы введете СУММ, а у вас английский язык интерфейса Google Таблиц, то она автоматически преобразуется в SUM), а вот в Excel будет ошибка #ИМЯ? (#NAME?): приложение не распознает функцию на другом языке.
В Excel можно выбрать функцию на вкладке ленты «Формулы» в одной из категорий.
Нажмите на кнопку fx справа от строки формул, чтобы открыть окно «Вставка функции».
Также его можно открыть с помощью сочетания клавиш Shift + F3.
Отличие в том, что с окном «Вставка функции» вы будете видеть отдельные поля с аргументами функции. Многим, особенно начинающим, пользователям так проще вводить функции.
Тогда как при вводе функции и ее аргументов непосредственно в ячейке будет только всплывающая подсказка с аргументами.
Она тоже может помочь, если знать несколько нюансов.
1. Текущий аргумент выделяется полужирным. Если щелкнуть по нему, то он будет выделен целиком (это помогает при работе с длинными формулами).
2. Сам список аргументов можно переносить: иногда он мешает при вводе формулы, закрывая данные. Это можно сделать с зажатой левой кнопкой мыши. Главное — тянуть за пустое пространство, а не за названия аргументов.
3. Необязательные аргументы отображаются в квадратных скобках.
4. Если есть многоточие, значит, аргументов может быть больше. Например, у функции СУММПРОИЗВ / SUMPRODUCT (о которой мы поговорим прямо сейчас) могут перемножаться два диапазона, а могут больше.
Файл с примером: СУММПРОИЗВ.xlsx
Если вам нужно перемножить значения в двух столбцах, а потом сложить полученные произведения (например, умножить часовую ставку на количество отработанных часов по каждому сотруднику), нет смысла производить промежуточные вычисления.
Можно сразу получить результат с помощью функции СУММПРОИЗВ / SUMPRODUCT. Эта функция возвращает сумму произведений значений из нескольких (как минимум двух, как в нашем случае) массивов. Аргументы функции — несколько диапазонов одинакового размера, значения из которых нужно попарно перемножить и потом сложить произведения.
=СУММПРОИЗВ(B2:B36;C2:C36)
Каждая пара значений из двух диапазонов перемножается (1600 умножается на 9, 1600 на 6 и так далее), а затем полученные результаты складываются, но эти вычисления не приходится вводить в виде отдельных формул.
Таблицы Excel (Tables)
Файл с примерами: Таблицы.xlsx
Таблица Excel (Tables) — это диапазон (область), у которого есть имя, границы (саморасширяющиеся при добавлении данных снизу и справа), на который можно ссылаться из формул по именам этого диапазона (таблицы) и его столбцов. Таблицы появились в Excel 2007 (до этого они назывались списками и имели меньший функционал).
Конечно, тут может возникнуть непонимание: что это за «таблица» внутри Excel, когда весь Excel и есть табличный редактор и мы привыкли воспринимать диапазоны в нем как таблицы (и называть так же)?
Возможно, поэтому в русскоязычном пространстве прижилось название «умные таблицы» — они действительно имеют много преимуществ и заслуживают такого названия. Так что в обучающих видео, публикациях и книгах на русском вы можете встретить такое название, но в Excel это просто «таблица» (Table), а несколько обычных ячеек, не превращенных в таблицу, — это диапазон (Range).