Как сделать ссылку на значение в excel?

Один из читателей нашего сайта SirExcel задал вопрос, который может встречаться очень часто. Вопрос заключался в следующем «Как сделать так, чтобы на одном листе показывалась информация из определенной ячейки к примеру $A$1 но с каждого листа из книги? Листов очень много надо сделать сводный список содержимого определенной ячейки со всех листов.»

Я как раз собирался написать о том, как работает функция ДВССЫЛ, почему бы не рассмотреть ее именно на этом примере.

Итак, мы имеем файл Excel, в котором много листов. Давайте рассмотрим простой пример, где названия листов у нас не менялись. То есть у нас есть Лист1, Лист2, Лист3 и так далее.

• Допустим, что на каждом листе (Лист1-Лист5) у нас есть данные по выручке 5 различных магазинов в виде одинаковых таблиц данных.
• 
• На листе 6 есть результирующая таблица, где нам необходимо заполнить ее данными, взяв их со всех листов.
• 
• Таким образом, чтобы заполнить данные за январь нам необходимо взять данные по выручке магазинов, находящихся в ячейке B2, но на 5 различных листов.
• Конечно, в нашем примере это можно было бы сделать в ручную, указав просто ссылки на соответствующие значения, но если листов очень много, или данных очень много, то вручную это сделать будет очень затруднительно.

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

ДВССЫЛ – функция, которая возвращает ссылку заданную текстовой строкой.

Чтобы понять принцип действия данной функцию, давайте рассмотрим такой пример.

Посмотрите на рисунок ниже. В ячейке А1 написан текст D4, а в самой ячейке D4 указано число 9999. Если мы напишем формулу ДВССЫЛ(A1), то в результате получим число 9999. То же самое мы получим если напишем формулу так:  =ДВССЫЛ(«D4»)  То есть мы написали текстом адрес ячейки D4 и функция ДВССЫЛ вернула нам то значение, которое находится по данному адресу (D4).

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

=Лист1!B2 (Выручка за январь магазина 1)

=Лист2! В2 (Выручка за январь магазина 2) и т.д. 

Но сложность заключается в том, что если ячейку просто протянуть вниз, то Лист1 не будет меняться на Лист2 и так далее.

Если мы пропишем формулу  ДВCСЫЛ(«Лист1!B2»), ДВCСЫЛ(«Лист2!B2») и так далее, то функция будет возвращать нам то же самое, но это так же не решает нашу проблему, ведь «Лист1!B2» прописан обычным текстом и так же не будет изменяться при протягивании.

Для решения нашей задачи мы разделим текст «Лист1!B2» на две части (отдельно «Лист1» и отдельно «!B2» — обратите внимание на восклицательный знак во второй части текста) и потом их склеим. Текст Лист1, Лист2 и так далее мы пропишем напротив соответствующих магазинов, при этом написав Лист1, мы сможем просто протянуть ячейку вниз и автоматически получить список Лист1, Лист2 и так далее.

1. Теперь сцепим этот текст внутри функции ДВССЫЛ с помощью знака & и получим формулу, которая будет автоматически протягиваться и при этом будут подтягиваться данные из одной и той же ячейки, но разных листов.
2. =ДВССЫЛ(A2&»!B2″)
3. 

Итоговая формула выглядит именно так, но я специально расписал с самого начала по шагам, чтобы было понятно из чего состоит данная формула.

В принципе, этого уже достаточно, чтобы за короткий срок решить задачу, указанную в начале статьи.

Чтобы заполнить данные за февраль, нам необходимо в ячейке D2 написать такую же формулу, но поменять текст «!B2» на «!B3″  =ДВССЫЛ(A2&»!B3″) и протянуть вниз, аналогично за март.

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

Итак, мы решили задачу, как быстро заполнить данные за определенный месяц, но нам необходимо заполнить данные за остальные месяца. Мы знаем, что данные за Январь соответствуют ячейке B2, февраль — B3, Март — B4.

То есть буква «B» остается неизменной, меняется лишь цифра. Зная это мы просто разобьем  текст внутри ссылки ДВССЫЛ не на 2 части, а на три. Добавим дополнительно строку над таблицей и напишем сверху цифры соответствующие ячейки месяца.

Пропишем формулу.

=ДВССЫЛ(A3& «!B»&C1), где A3 — это текст «Лист1», «!B» — это неизменный текст и С1 — это цифра 2. Все это объединено с помощью знака & в общий текст «Лист1!B2». Нам также потребуется закрепить столбец A (с помощью знака $), чтобы он не менялся при протягивании формулы вправо и строку 1, чтобы она не менялась при протягивании формулы вниз. Мы получаем следующую итоговую формулу.

=ДВССЫЛ($A3&»!B»&C$1) которую можно протянуть вправо и вниз.

Заметки по теме:

• Если бы листы назывались у нас Магазин 1, Магазин 2 и так далее, то дополнительный столбец со словами Лист1, Лист2 и так далее нам бы не потребовался.
• Функцию ДВССЫЛ используют часто тогда, когда требуется изменить ссылку на ячейку в формуле, не изменяя саму формулу.

Если вам понравилась статья, пожалуйста, нажмите +1 и «Мне нравится». Так же подписывайтесь на нашу рассылку или вступайте в нашу группу ВКонтакте, чтобы не пропустить наши следующие уроки по Excel

SirExcel — безграничные возможности Excel

Источник: https://sirexcel.ru/priemi-excel/ssylki-na-odnu-i-tu-zhe-yachejku-s-raznyx-listov/

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

39315 27.10.2012 Скачать пример

Если вы работаете в Excel не второй день, то, наверняка уже встречали или использовали в формулах и функциях Excel ссылки со знаком доллара, например $D$2 или F$3 и т.п. Давайте уже, наконец, разберемся что именно они означают, как работают и где могут пригодиться в ваших файлах.

Относительные ссылки

Это обычные ссылки в виде буква столбца-номер строки ( А1, С5, т.е. «морской бой»), встречающиеся в большинстве файлов Excel. Их особенность в том, что они смещаются при копировании формул. Т.е.

C5, например, превращается в С6, С7 и т.д. при копировании вниз или в D5, E5 и т.д. при копировании вправо и т.д.

В большинстве случаев это нормально и не создает проблем:

Смешанные ссылки

Иногда тот факт, что ссылка в формуле при копировании «сползает» относительно исходной ячейки — бывает нежелательным. Тогда для закрепления ссылки используется знак доллара ($), позволяющий зафиксировать то, перед чем он стоит. Таким образом, например, ссылка $C5 не будет изменяться по столбцам (т.е.

С никогда не превратится в D, E или F), но может смещаться по строкам (т.е. может сдвинуться на $C6, $C7 и т.д.). Аналогично, C$5 — не будет смещаться по строкам, но может «гулять» по столбцам.

Такие ссылки называют смешанными:

Абсолютные ссылки

Ну, а если к ссылке дописать оба доллара сразу ($C$5) — она превратится в абсолютную и не будет меняться никак при любом копировании, т.е. долларами фиксируются намертво и строка и столбец:

Самый простой и быстрый способ превратить относительную ссылку в абсолютную или смешанную — это выделить ее в формуле и несколько раз нажать на клавишу F4. Эта клавиша гоняет по кругу все четыре возможных варианта закрепления ссылки на ячейку: C5 → $C$5 → $C5 → C$5 и все сначала.

Все просто и понятно. Но есть одно «но».

Предположим, мы хотим сделать абсолютную ссылку на ячейку С5. Такую, чтобы она ВСЕГДА ссылалась на С5 вне зависимости от любых дальнейших действий пользователя. Выясняется забавная вещь — даже если сделать ссылку абсолютной (т.е.

$C$5), то она все равно меняется в некоторых ситуациях. Например: Если удалить третью и четвертую строки, то она изменится на $C$3. Если вставить столбец левее С, то она изменится на D.

Если вырезать ячейку С5 и вставить в F7, то она изменится на F7 и так далее.

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

Действительно абсолютные ссылки

Решение заключается в использовании функции ДВССЫЛ (INDIRECT), которая формирует ссылку на ячейку из текстовой строки. 

• Если ввести в ячейку формулу:
• =ДВССЫЛ(«C5»)
• =INDIRECT(«C5»)

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

Единственная небольшая сложность состоит в том, что если целевая ячейка пустая, то ДВССЫЛ выводит 0, что не всегда удобно.

Однако, это можно легко обойти, используя чуть более сложную конструкцию с проверкой через функцию ЕПУСТО:

=ЕСЛИ(ЕПУСТО(ДВССЫЛ(«C5″));»»;ДВССЫЛ(«C5»))

=IF(ISBLANK(INDIRECT(«C5″));»»;INDIRECT(«C5»))

Ссылки по теме

Источник: https://www.planetaexcel.ru/techniques/2/83/

Шаг #3. Относительные и абсолютные ссылки (изучаем Excel с нуля)

Для того, чтобы сделать таблицу еще более гибкой и удобной, нам потребуются некоторые дополнительные знания. То есть опять немного теории…

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

Автозаполнение формул (иллюстрация с предыдущего шага)

• Программа автоматически «поняла» нашу задумку и значения адресов ячеек в формулы были подставлены правильные, но ка это произошло?
• Практически во всех формулах используются адреса ячеек, а правильнее сказать — ссылки на ячейки, что позволяет нам использовать в формулах значения, которые находятся или рассчитываются в других ячейках.
• Если в формуле сделана ссылка на ячейку В3 (в которой находится какое-то число) и мы поменяем значение в этой ячейке, то автоматически будет пересчитана и формула.
• Поясню на примере.
• В ячейку B3 введем цифру 2, а в ячейку B4 вставим следующую формулу: = B3+3
• Какой будет результат вычислений?
• Правильно, после нажатия клавиши Enter в ячейке B4 отобразится вычисленное значение — 5.
• Что произойдет, если мы изменим значение ячейки B3 с 2 на 4?

Верно! Значение в ячейке B4 автоматически пересчитается и будет равным 7. Это означает, что формула будет рассчитываться в соответствии с новым значением ячейки В3.

Если бы не было ссылок (в нашем примере это ссылка на ячейку B3), то нам бы пришлось вручную изменять все формулы при изменении какого-то одного ее компонента.

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

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

То же самое происходит и при фактическом копировании. Чтобы в этом убедиться, давайте скопируем ячейку В8.

Для этого выделим ячейку (1) и щелкнем на ней правой кнопкой мыши для вызова контекстного меню (2).

Копирование содержимого ячейки B8

Выберем из контекстного меню пункт Копировать. Ячейка выделилась динамической рамкой.

Скопированная ячейка

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

Теперь установим табличный курсор в пустую ячейку В19 и вставим скопированную информацию. Для этого мы также можем воспользоваться контекстным меню, но мы задействуем панель инструментов Буфер обмена на вкладке Главная — нажимаем на кнопку Вставить и получаем результат.

Вставка значения из буфера обмена

Если мы посмотрим на формулу в ячейке B19, то увидим в ней ссылку на ячейка В18.

Относительные ссылки в формулах

Понимаете что произошло? Это очень важно понять!

Мы копировали ячейку В8, в которой в формуле использовалась ссылка на вышестоящую ячейку В7 (1, см. рис. выше), то есть использовалась ссылка на ячейку, которая была выше ячейки с формулой.

Именно так она и была скопирована — в ячейке В19 (2, см. рис. выше) в формуле используется ссылка на вышестоящую ячейку В18.

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

Для проверки попробуйте ответить на вопрос — если сейчас скопировать формулу из ячейки В7 (см. рис. выше), которая у нас несколько отличается от формул в других ячейках, и вставить ее в ячейку В20, то ссылка на какую ячейку мы увидим в итоге?

1. Ответили?
2. Давайте проверим — в формулу войдет ячейка B16, которая находится выше на четыре ячейки, как и ячейка В3, относительно ячейки В7.
3. Вот такие ссылки называются относительными, так как в формулу подставляется значение из ячейки, расположенной на определенном расстоянии относительно ячейки, с которой мы работаем.

А что делать, если нужно, чтобы в формуле участвовало значение из какой-то конкретной ячейки? Все логично — нужно использовать абсолютные ссылки, то есть ссылки на конкретные ячейки таблицы.

Как мы знаем, адрес ячейки состоит из обозначения столбца и строки, например, ячейка B3 находится на пересечении третьей строки и столбца B.

Чтобы сделать ссылку на ячейку абсолютной, нужно поставить знак $ перед обозначением столбца и строки (знак доллара ставится с помощью сочетаний клавиш Shift + 4). То есть, если я хочу, чтобы в формуле у меня всегда участвовало значение из ячейки В3, то изменю В3 на $B$3.

Давайте так и сделаем — изменим формулу в ячейке В7:

Абсолютная ссылка

Абсолютная и относительная ссылки в одной формуле

Абсолютная ссылка по-прежнему ведет на ячейку B3, а относительная изменилась и теперь указывает на ячейку B17.

То есть знаком $ мы как бы запрещаем Экселю изменять столбец или строку в адресе ячейки и он всегда будет в неизменном виде.

При этом мы можем запрещать изменять в ссылке что-то одно — либо столбец, либо строку. Такая ссылка будет называться смешанной. Такие ссылки выглядят так — B$2 или $B2, что, соответственно, запретит Эксель менять адрес строки или столбца.

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

В ячейке А4 напишем — Процент, а в ячейку В4 подставим значение процента по вкладу — 11.

Выносим проценты из формулы в отдельную ячейку

Теперь изменим формулы — меняем в ячейке В7 значение процента на абсолютную ссылку — $B$4, затем тоже самое проделаем в ячейке В8:

Применение абсолютных ссылок

Растиражируем с помощью автозаполнения формулу из ячейки B8 на остальные ячейки столбца B.

Автозаполнение формул

Что мы получили в итоге? А то, что теперь мы можем менять и сумму вклада, и процент, а остальные данные будут вычисляться автоматически!

Вычисление общего дохода

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

Именно это мы и сделаем на следующем шаге.

Если вам удобнее воспринимать информацию в видео-формате, то посмотрите ролик:

Если вам интересен Эксель, то подписывайтесь на этот Дзен-канал или мой видео канал на YouTube.

Источник: https://zen.yandex.ru/media/id/5c4d8a530aaeef00ac928ac3/5c53fded58518100ad039758

Ссылки в Excel

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

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

при работе с большими массивами данных формула прописывается обычно один раз, а затем «протягивается» (копируется) на другие ячейки (например, в итоговой строке или итоговом столбце таблицы). Корректность такого «протягивания» сильно зависит от типа ссылки. Здесь есть два варианта.

Первый: диапазон, на который ведет ссылка, «перемещается» вслед за копируемой ячейкой, то есть остается на одном месте относительно ячейки с формулой. Второй: ссылка ведет на один и тот же адрес независимо от того, куда копируется формула.

Недооценка важности вида ссылки приводит к ошибкам в расчетах и необходимости коррекции формул. Все это как минимум тормозит работу. Помните, что каждое лишнее действие вносит свой вклад в бесполезно потраченное время.

Рассмотрим простой пример. Нужно сложить два числа. Сделать это легко, прописав в свободной ячейке (например, внизу) знак «=» и затем через знак «+» сослаться на складываемые ячейки. Если чисел много, то суммировать лучше через функцию СУММ, указав сразу весь диапазон суммирования.

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

Относительные ссылки Excel

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

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

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

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

Абсолютные ссылки

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

Теперь попробуем «протянуть» формулу вниз для расчета остальных долей. Сумма долей должна получиться ровно 100%. Однако уже на втором значении видно: что-то пошло не так.

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

=$A$1

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

Смешанные ссылки

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

=A$1

=$A1

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

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

Как быстро установить символ доллара $

Если ставить значок доллара с клавиатуры классическим методом, то потребуется вначале перейти в английскую раскладку, а затем нажать Shift+4. Скажем прямо, это долго и неудобно.

Гораздо быстрее войти в режим редактирования формул с помощью клавиши F2 (если вы набираете формулу вручную, то уже находитесь в этом режиме), установить курсор на нужной ссылке и нажать F4. Ссылка начнет менять свой режим «абсолютности».

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

И так по кругу. Снова абсолютная, только строка, только столбец, относительная и т.д. Последовательным нажатием F4 останавливаетесь на нужном варианте.

Очень удобно, не нужно переключать раскладку и искать $. Эта же кнопка работает и в диалоговом окне «Вставки функции». Например, при написании формулы ВПР или СУММЕСЛИ нужно внимательно смотреть на ссылающиеся диапазоны, поэтому значки $ используются очень часто.

Как я уже говорил выше, если диапазон, на который нужно сослаться находится в той же книге (файле Excel), пусть даже и на другом листе, то Excel по умолчанию устанавливает относительные ссылки (без значка «$»). А вот ссылки на другие книги Excel по умолчанию делает абсолютными (вставляет «$» где только можно).

Но про другие листы и книги поговорим в следующем параграфе.

Ссылки на другие листы и книги

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

Во-первых, при открытии файла с внешними связями Excel назойливо напоминает об этом выскакивающим посреди экрана окошком. Это, конечно, помогает не забывать, что в файле есть внешние ссылки, но лучше, чтобы их вообще не было.

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

Все внутренние формулы останутся неизменными, а формулы с внешними ссылками превратятся в значения. Убрать ссылки Excel можно и по-другому. Для этих же целей можно использовать более топорный метод. Нужно просто скопировать диапазон с формулами и тупо вставить его сверху в виде значений (через специальную вставку). Правда, в значения превратятся уже все формулы, а не только внешние.

Стиль ссылок

И еще кое-что про ссылки. Иногда они выглядят несколько непривычно. Это особый стиль ссылок, при котором адрес ячейки A1 имеет наименование R1C1 (первая строка (R1), первая колонка (C1)).

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

Для этого нужно выполнить следующие действия: Файл → Параметры → Формулы и далее нужно снять галочку с пункта Стиль ссылки R1C1.

Всего доброго и до новых встреч.

Поделиться в социальных сетях:

Источник: https://statanaliz.info/excel/funktsii-i-formuly/ssylki-v-excel/

Трюк №64. Как сделать, чтобы формула ссылалась на следующие строки при копировании по столбцам

Автоматическое приращение ссылок на ячейки в Excel в большинстве случаев работает прекрасно, но иногда нужно изменить принцип работы этого средства.

Если создать ссылку на одну ячейку, например, А1, а затем скопировать ее вправо по столбцам, то в результате эта ссылка будет меняться: =В1, =С1, =D1 и т. д.

, но на самом деле вы хотите получить другой результат. Нужно, чтобы приращение формулы шло по строкам, а не по столбцам, то есть =А1, =А2, =А3 и т. д. К сожалению, в Excel такая возможность не предусмотрена. Но можно обойти это ограничение с помощью функции ДВССЫЛ (INDIRECT), в которую вложена функция АДРЕС (ADDRESS).

Наверное, лучший способ объяснить, как создать нужную функцию, — использовать пример с предсказуемыми результатами. В ячейках А1:А10 по порядку введите числа от 1 до 10.

Выделите ячейку D1 и введите в ней следующую формулу: =INDIRECT(ADDRESS(COLUMN()-3;1), в русской версии Excel =ДВССЫЛ(АДРЕС(СТОЛБЕЦ()-3;1)). Когда вы сделаете это, в ячейке D1 должно появиться число 1. Это происходит потому, что формула ссылается на ячейку А1.

Если вы скопируете эту формулу в столбец справа, то в ячейке Е1 появится число 2. Другими словами, хотя вы копируете формулу по столбцам, приращение ссылки в формуле идет по строкам.

Если продолжить копировать эту формулу вправо по столбцам, в ячейке F1 появится число 3, в ячейке G1 — число 4 и т. д.

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

Чтобы продемонстрировать, о чем идет речь, будем использовать необходимую всем функцию СУММ (SUM). Предположим, вы получили длинный список чисел, и необходимо просуммировать столбцы чисел в режиме промежуточной суммы: =SUM($A$1:$A$2). =SUM($A$1:$A$3). =SUM($A$1:$A$4), в русской версии Excel =СУММС$А$1:$А$2). =СУММ($А$1:$А$3). =СУММ($А$1:$А$4).

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

Вместо этого можно применить тот же принцип, что и ранее, при ссылке на одну ячейку.

Заполните диапазон А1:А100 числами от 1 до 100 по порядку. В ячейку А1 введите 1, выделите ячейку А1 и, удерживая клавишу Ctrl, щелкните маркер заполнения и протащите «го вниз на 100 строк.

Выделите ячейку D1 и введите следующую формулу: =SUM(INDIRECT(ADDRESS(1;1)&»:»&ADDRESS(COLUMN()-2;1))), в русской версии Excel =СУММ(ДВССЫЛ(АДРЕС(1;1)&»:»&АДРЕС(СТОЛБЕЦ()-2;1))). В результате вы получите 3 как сумму ячеек А1:А2.

Скопируйте эту формулу в ячейку Е1, в результате получите б, то есть сумму ячеек А1:А3. Скопируйте ее в ячейку F1, получите 10 — сумму ячеек А1:А4.

Переменная функция СТОЛБЕЦ (COLUMN) заставляет последнюю ссылку на ячейку увеличиваться на 1 каждый раз, когда вы копируете формулу в новый столбец.

Функция СТОЛБЕЦ (COLUMN) всегда возвращает номер столбца (а не букву) в ту ячейку, в которой находится, если только вы не ссылаетесь на другую ячейку. Иначе, можно было бы воспользоваться средством Excel Специальная вставка → Транспонировать (Paste Special → Transpose).

Введите формулу =SUM($A$1:$A2) в ячейку В1 (обратите внимание на относительную ссылку на строку и абсолютную ссылку на столбец в ссылке $А2) и скопируйте эту формулу вниз до ячейки В100.

Источник: http://Excel2010.ru/hack-64.html