Коллеги, добрый день! В этом видео обсудим, какие бывают ссылки на ячейки в Google Таблицах, как ссылаться на данные из других листов и какие бывают диапазоны в Таблицах. Это фундаментальные для работы с формулами знания, ведь при работе с формулами мы используем данные из отдельных ячеек и диапазонов, то есть из групп ячеек. Давайте посмотрим, как это работает на практике. Начнем с относительных и абсолютных ссылок. Обсудим, что такое ссылка вообще — это указание на ячейку того же листа или другого листа документа, возможность использовать данные из других ячеек в качестве аргументов ваших формул. Мы начнем с тривиальной формулы, когда мы просто ссылаемся на другую ячейку. Любая формула начинается со знака "=", и достаточно просто щелкнуть на одну из ячеек, например, на ячейку B2 в нашем файле, после чего мы получим отображение содержимого этой ячейки в нашей целевой ячейке, в той, где стоит формула, B2. Соответственно, в формулах используется адресация "морской бой" — нумерация ячеек из буквенного обозначения столбца и цифрового обозначения строки. Но что на самом деле означает B2 в формуле? Является ли это фиксированным адресом или нет? Что если протянуть за маркер в правом нижнем углу формулы вправо и вниз? Что мы увидим? Что следующие формулы, которые мы получили путем протягивания, ссылаются уже на другие ячейки. Не B2, а C2, D2 и так далее. И если ниже смотреть — B3, B4 и так далее. Что же получается? Что ссылка B2 — это не ссылка на конкретную ячейку? На самом деле, это действительно так. B2 — это ссылка на ячейку, которая находится левее текущей, то есть это ссылка на ячейку, которая стоит пятью столбцами левее. И когда мы формулу протягиваем, это правило сохраняется, мы по-прежнему ссылаемся на ячейку на пять столбцов левее, то есть мы смотрим относительно ячейки с формулой, а не на конкретный адрес. Так что равно B2 — это не ссылка на В2, это, на самом деле, ссылка на какую-то ячейку, которая как-то стоит относительно текущей. И все зависит от того, в какой ячейке вы эту формулу ввели. В данном случае — от ячейки G2, эта ячейка на пять столбцов левее. Соответственно, когда мы протягиваем, формулы меняются, а это отношение сохраняется. Поэтому обычная ссылка такого вида и называется относительной. А вот если вам нужно ссылаться ровно на определенную ячейку с конкретным адресом, и чтобы при протягивании формул ничего не происходило с ней, для этого нужна ссылка так называемая абсолютная. Чтобы ее создать, нужно также сослаться на ячейку (кстати, это можно сделать не только мышкой, но и написав просто клавиатурой B2), а затем нажать клавишу "F4", после чего появятся два доллара: перед номером столбца, перед номером строки. Кстати, эти доллары можно было ввести и с клавиатуры, но пользоваться "F4" удобнее. И вот эта запись с долларами означает, что это ссылка именно на B2, неважно, откуда вы ссылаетесь. Если такую формулу тянуть, мы увидим, что в любом месте она все равно возвращает единицу, то есть отображает содержимое ячейки B2. Если зайти в формулу, всегда видны ее аргументы. Вот так пунктиром сейчас выделена ячейка B2. То есть это ссылка абсолютная. Вопрос: для чего на практике это нужно? У нас есть вкладка "Налог", где есть продажи по периодам в столбце A и есть ставка налога в столбце E (20 процентов), и мы хотим их перемножить. Как и в Excel, в Таблицах можно перемножить несколько ячеек между собой, как и произвести другие арифметические операции с ними, то есть сослаться на ячейку A2, например, поставить знак умножения и умножить ячейку A2 на содержимое ячейки E1. Мы получим результат одну пятую, 20 процентов от продаж в ячейке A2. Но если мы захотим эту формулу протянуть дальше вниз, можно сделать это, удерживая зажатой кнопку мыши вот таким образом, либо можно это сделать, щелкнув двойным щелчком на маркер в правом нижнем углу, и тогда по всей таблице формула протянется. Но как мы видим, во всех строках, кроме первой, были получены нули. Почему? Давайте посмотрим. На самом деле, потому что в первой строке мы ссылались на ячейку слева от нас (ссылка A2) и на ячейку справа от нас и выше (E1), и эта тенденция сохраняется ниже. То есть мы все время перемножаем ячейку слева от нас (продажи) и ячейку на несколько столбцов правее и выше на строку. Это уже E3 в четвертой строке. А что нам нужно на самом деле перемножать? Нам действительно нужно брать ячейку слева от формулы и брать ставку налога, которая всегда в одной и той же ячейке E1. Поэтому мы закрепляем ссылку на ставку налога, то есть нажимаем "F4", и протягиваем такую формулу, протягиваем ее вниз. Получаем результат. То есть у нас одна ссылка относительная, она всегда меняется в зависимости от той строки, в которой мы находимся. Это продажи из соответствующей строки. А вторая ссылка, второй множитель всегда абсолютный, фиксированный, это всегда ссылка на E1. Собственно говоря, в большинстве случаев понимание того, как работают относительные и абсолютные ссылки, нужно для работы с формулами. Давайте вернемся к типам ссылок и поговорим еще о смешанных ссылках. Мы с вами рассмотрели относительные и абсолютные, а что будет, если поставить ссылку на B2, нажать "F4" несколько раз? То есть изначально фиксируются строки и столбцы (два доллара), но если нажать еще раз, то будет зафиксирована только вторая строка, "B$2". Такая ссылка означает, что строка никак меняться не будет, но столбец не фиксирован, столбец будет меняться. То есть если мы протянем формулу вправо, мы будем ссылаться на другие столбцы, B2 меняется на C2 и D2, соответственно. Но при смещении формулы вниз или вверх вторая строка никуда не денется. Мы всегда ссылаемся на разные столбцы одной второй строки. И если же также поставить ссылку на ячейку и нажать "F4" уже трижды, то будет ссылка с закрепленным столбцом, столбец B, перед ним стоит доллар. Если протягивать такую формулу, то мы увидим, что меняются только строки в этой ссылке, но не меняются столбцы, это всегда столбец B. Вопрос: для чего такие смешанные ссылки нужны на практике? Давайте посмотрим на такую таблицу на вкладке "Производство", где у нас есть разные сценарии по объему производства от 10 до 55 с шагом пять. И есть разные варианты оптовой цены от 10 до 20. И мы хотим на пересечении каждой ячейки видеть произведение. Понятно, что нам нужно умножить первые числа, а потом протянуть формулу. Но если мы прямолинейно попробуем это сделать, мы увидим, что получаются огромные числа, которые даже не отображаются. Помните про экспоненциальный формат? Так вот, у нас здесь числа с двумя сотнями нулей, это очень много. А почему так произошло? Щелкнем на формулу и увидим, что мы всегда перемножаем число слева от нас и сверху от нас, а никак уже не работаем на самом деле с множителями из этих синих строк, где у нас прописаны разные сценарии по объемам производства и оптовой цене. А нужно-то нам на самом деле перемножать в рамках вот этой строки и в рамках вот этого столбца, но мы ничего не закрепляли. Нам нужно это сделать. Если мы закрепим целиком ячейки, то мы всегда будем умножать 10 на 10, то есть ничего не произойдет, а нам нужно перемножать разные числа из второй строки. Поэтому я фиксирую в первом множителе только вторую строку, и пусть столбцы меняются. А в случае с B3 я фиксирую только столбец B, в рамках которого будут меняться строки, разные варианты оптовой цены. И теперь мы протянем эту формулу до конца и получим правильный вариант, правильную таблицу, где берутся разные строки из столбца B и разные столбцы из строки два и перемножаются между собой. Перейдем на следующую вкладку и теперь поговорим про разные типы диапазонов в Google Таблицах на таком простом примере, где есть 12 месяцев продаж за определенный год, и попытаемся их просуммировать с помощью функции СУММ, которая просто суммирует какой-либо диапазон. Она многим известна и по Excel, это, как правило, первая функция, с которой мы знакомимся, работая с таблицами. Если мы хотим просуммировать все продажи за 12 месяцев, мы сделаем так: "B2:B13", — и получим результат. Но что если мы хотим предусмотреть пополнение таблицы, добавление новых строк? Вот таким образом. Они же не будут учитываться в этом суммировании. Вам придется каждый раз менять диапазон. Есть выход, например, в Excel можно было бы сделать так: выделить столбец целиком (и в Google Таблицах это тоже допускается), и тогда будут суммироваться все строки этого столбца. Но есть проблема: в расчет попал заголовок. Там тоже стоит число, это номер года. И видите, его неправильно считать. Поэтому все-таки надо начинать суммирование со строки два. И если в Excel такая запись выдала бы ошибку (B2:B), то в Google Таблицах такой диапазон (назовем его открытым, то есть без последней строки) допустим. Это суммирование всех строк в столбце B, начиная со второй. И сколько бы не было новых значений, у вас всегда будет происходить суммирование всех этих чисел. Даже если пользователь потом добавит новые строки в документе, и в них добавит числа, суммирование будет происходить автоматически. Это очень удобно для тех таблиц, которые пополняются в течение года, например, новыми сделками или новыми сотрудниками. Это работает и для столбцов, то есть если мы заполним несколько чисел случайным образом по столбцам (давайте ограничимся пока тремя), мы можем суммировать и диапазон, у которого не будет последнего столбца. Удалим последний столбец, и такая запись будет означать, что мы суммируем все ячейки в строке 32, начиная с первого столбца и вплоть до конца таблицы, сколько бы ни появлялось тут новых столбцов, вот таким образом, и сколько бы в них не появилось чисел, они будут попадать в расчет. Давайте теперь поговорим про ссылки на другие документы, на другие листы, вкладки. Когда мы ставим знак "=" и хотим сослаться на какую-либо ячейку другого листа, что происходит? Появляется название этого листа в апострофах, затем восклицательный знак и ссылка на ячейку. То есть если в рамках одного листа мы могли ссылаться на ячейку просто по ее адресу, закреплять ее или нет, то если нам нужно с одного листа забирать данные из другого листа, нам нужно указывать его имя. Это происходит автоматически, когда вы ссылаетесь таким образом, но это также можно сделать и в ручном режиме самостоятельно с клавиатуры, написав название листа. При этом, если оно состоит из одного слова, например, наш лист "Налог", можно просто написать Налог, апострофы появятся автоматически. Но если название листа состоит из нескольких слов с пробелами, апострофы вам придется указать, чтобы Google Таблицы понимали, что речь идет про название листа, а не какие-то отдельные сущности через пробел. А после названия листа всегда идет восклицательный знак и ссылка на ячейку или диапазон. Итак, коллеги, мы с вами обсудили основополагающую для работы с вычислениями тему "Абсолютные и относительные ссылки на ячейки в Google Таблицах" и узнали, как формируются ссылки с одного листа на другой, также познакомились с открытыми диапазонами и обсудили, зачем они нужны на практике.
