Содержание

Как сделать формулу в excel с одного листа на другой?

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

Excel позволяет делать ссылки в формулах и функциях на другие листы и даже книги. Можно сделать ссылку на данные отдельного файла. Кстати в такой способ можно восстановить данные из поврежденного файла xls.

Ссылка на лист в формуле Excel

Доходы за январь, февраль и март введите на трех отдельных листах. Потом на четвертом листе в ячейке B2 просуммируйте их.

Возникает вопрос: как сделать ссылку на другой лист в Excel? Для реализации данной задачи делаем следующее:

Заполните Лист1, Лист2 и Лист3 так как показано выше на рисунке.
Перейдите на Лист4, ячейка B2.
Поставьте знак «=» и перейдите на Лист1 чтобы там щелкнуть левой клавишей мышки по ячейке B2.
Поставьте знак «+» и повторите те же действия предыдущего пункта, но только на Лист2, а потом и Лист3.
Когда формула будет иметь следующий вид: =Лист1!B2+Лист2!B2+Лист3!B2, нажмите Enter. Результат должен получиться такой же, как на рисунке.

Как сделать ссылку на лист в Excel?

Ссылка на лист немного отличается от традиционной ссылки. Она состоит из 3-х элементов:

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

Ссылка на лист в другой книге Excel

Ссылка на лист в другой книге имеет уже 5 элементов. Выглядит она следующим образом: =’C:DocsЛист1′!B2.

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

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

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

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

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

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

Допустим, что на каждом листе (Лист1-Лист5) у нас есть данные по выручке 5 различных магазинов в виде одинаковых таблиц данных.

На листе 6 есть результирующая таблица, где нам необходимо заполнить ее данными, взяв их со всех листов.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Формулы в Excel – одно из самых главных достоинств этого редактора. Благодаря им ваши возможности при работе с таблицами увеличиваются в несколько раз и ограничиваются только имеющимися знаниями. Вы сможете сделать всё что угодно. При этом Эксель будет помогать на каждом шагу – практически в любом окне существуют специальные подсказки.

Как вставить формулу

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

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

Из чего состоит формула

символ «=» – с него начинается любая формула;
функция «СУММ»;
аргумента функции «A1:C1» (в данном случае это массив ячеек с «A1» по «C1»);
оператора «+» (сложение);
ссылки на ячейку «C1»;
оператора «^» (возведение в степень);
константы «2».

Использование операторов

Операторы в редакторе Excel указывают какие именно операции нужно выполнить над указанными элементами формулы. При вычислении всегда соблюдается один и тот же порядок:

Арифметические

Если перед числом поставить «минус», то оно примет отрицательное значение, но по модулю останется точно таким же.

Операторы сравнения

Данные операторы применяются для сравнения значений. В результате операции возвращается ИСТИНА или ЛОЖЬ. К ним относятся:

знак «меньше» — «=D1
- знак «меньше или равно» — «3″;B3:C3)
  1. Excel может складывать с учетом сразу нескольких условий. Можно посчитать сумму клеток первого столбца, значение которых больше 2 и меньше 6. И ту же самую формулу можно установить для второй колонки.
Математические функции и графики

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

В качестве примера попробуем построить графики для экспоненты и какого-нибудь уравнения. Инструкция будет следующей:
1. Создадим таблицу. В первой графе у нас будет исходное число «X», во второй – функция «EXP», в третьей – указанное соотношение. Можно было бы сделать квадратичное выражение, но тогда бы результирующее значение на фоне экспоненты на графике практически пропало бы.
Как мы и говорили ранее, прирост экспоненты происходит намного быстрее, чем у обычного кубического уравнения.

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

Отличие в версиях MS Excel

Всё описанное выше подходит для современных программ 2007, 2010, 2013 и 2016 года. Старый редактор Эксель значительно уступает в плане возможностей, количества функций и инструментов. Если откроете официальную справку от Microsoft, то увидите, что они дополнительно указывают, в какой именно версии программы появилась данная функция.

Во всём остальном всё выглядит практически точно так же. В качестве примера, посчитаем сумму нескольких ячеек. Для этого необходимо:
1. Указать какие-нибудь данные для вычисления. Кликните на любую клетку. Нажмите на иконку «Fx».
1. Выбираем категорию «Математические». Находим функцию «СУММ» и нажимаем на «OK».
1. Указываем данные в нужном диапазоне. Для того чтобы отобразить результат, нужно нажать на «OK».
1. Можете попробовать пересчитать в любом другом редакторе. Процесс будет происходить точно так же.
Заключение

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

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

Кроме этого, важно помнить, что формулы должны начинаться с символа «=» (равно). Многие начинающие пользователи забывают про это.

Файл примеров

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

Видеоинструкция

Если наше описание вам не помогло, попробуйте посмотреть приложенное ниже видео, в котором рассказываются основные моменты более детально. Возможно, вы делаете всё правильно, но что-то упускаете из виду. С помощью этого ролика вы должны разобраться со всеми проблемами. Надеемся, что подобные уроки вам помогли. Заглядывайте к нам чаще.

Как сделать формулу в excel с одного листа на другой?
Без функций и формул Excel был бы одной большой таблицей предназначенной для ручного заполнения данными. Благодаря функциям и формулам он является мощным вычислительным инструментом. А полученные результаты, динамически представляет в желаемом виде (если нужно даже в графическом).
[expert_bq id=»1570″]То же самое мы получим если напишем формулу так ДВССЫЛ D4 То есть мы написали текстом адрес ячейки D4 и функция ДВССЫЛ вернула нам то значение, которое находится по данному адресу D4. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Формулы в Excel – одно из самых главных достоинств этого редактора. Благодаря им ваши возможности при работе с таблицами увеличиваются в несколько раз и ограничиваются только имеющимися знаниями. Вы сможете сделать всё что угодно. При этом Эксель будет помогать на каждом шагу – практически в любом окне существуют специальные подсказки.

Функция INDIRECT (ДВССЫЛ) в Excel. Как использовать? ЭКСЕЛЬ ХАК
1. Заполните Лист1, Лист2 и Лист3 так как показано выше на рисунке.
2. Перейдите на Лист4, ячейка B2.
3. Поставьте знак «=» и перейдите на Лист1 чтобы там щелкнуть левой клавишей мышки по ячейке B2.
4. Поставьте знак «+» и повторите те же действия предыдущего пункта, но только на Лист2, а потом и Лист3.
5. Когда формула будет иметь следующий вид: =Лист1!B2+Лист2!B2+Лист3!B2, нажмите Enter. Результат должен получиться такой же, как на рисунке.
В данном самоучителе мы рассказали обо всем, что связано с формулами в редакторе Excel, – от самого простого до очень сложного. Каждый раздел сопровождался подробными примерами и пояснениями. Это сделано для того, чтобы информация была доступной даже для полных чайников.

Функция INDIRECT (ДВССЫЛ) в Excel. Как использовать?

Функция INDIRECT (ДВССЫЛ) в Excel используется когда у вас есть ссылки в виде текста, и вы хотите получить значения из этих ссылок.

Что возвращает функция

Функция возвращает ссылку, заданную текстовой строкой.

Синтаксис

Аргументы функции

Дополнительная информация
- Функция INDIRECT (ДВССЫЛ) это волатильная функция (используйте с осторожностью);
- Она пересчитывает значения каждый раз, когда вы открываете Excel файл, и каждый раз когда вычисление запускается на рабочем листе Excel;
- Так как волатильные функции постоянно обновляются и производят вычисления, это, в свою очередь, замедляет работу вашего Excel файла.
- Аргумент текстовой ссылки может выглядеть как:
  — ссылка на ячейку, которая содержит ссылку на ячейку в формате «A1» или «R1C1».
  — ссылка на ячейку в двойных кавычках.
  — именованный диапазон, возвращающий ссылку
Примеры использования функции ДВССЫЛ в Excel

Пример 1. Используем ссылку на ячейку для получения значения

Функция ДВССЫЛ получает ссылку на ячейку как исходные данные и возвращает значение ячейки по этой ссылке (как показано в примере ниже):

Функция получает ссылку на ячейку (в двойных кавычках) и возвращает значение этой ячейки, которая равна “123”.

Вы можете спросить — почему бы нам просто не использовать «=A1» вместо использования функции INDIRECT (ДВССЫЛ) ?

Если в данном случае вы введете в ячейку С1 формулу “=A1” или “=$A$1”, то она выдаст вам тот же результат, что находится в ячейке А1. Но если вы вставите в таблице строку выше, вы можете заметить, что ссылка на ячейку будет автоматически изменена.

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

Пример 2. Получаем данные по ссылке на ячейку

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

На примере выше, ячейка «А1» содержит в себе число “123”.

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

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

Пример 3. Используем комбинацию текстового и числового значений в функции INDIRECT (ДВССЫЛ)

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

Например, если в ячейке С1 указано число “2”, то используя формулу =INDIRECT(“A”&C1) или =ДВССЫЛ(«A»&C1) вы получите ссылку на значение ячейки «А2».

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

Пример 4. Ссылаемся на диапазон ячеек с помощью функции INDIRECT (ДВССЫЛ)

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

Например, =INDIRECT(“A1:A5”) или =ДВССЫЛ(«A1:A5») будет ссылаться на данные из диапазона ячеек «A1:A5».

Используя функцию SUM (СУММ) и INDIRECT (ДВССЫЛ) вместе, вы можете рассчитать сумму, а также максимальные и минимальные значения диапазона.

Пример 5. Ссылаемся на именованный диапазон значений с использованием функции INDIRECT (ДВССЫЛ)

Если вы создали именованный диапазон в Excel, вы можете обратиться к нему с помощью функции INDIRECT (ДВССЫЛ) .

Например, представим что у вас есть оценки по 5 студентам по трем предметам как показано ниже:

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

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

Например, если вы хотите узнать средний балл среди студентов по математике — используйте следующую формулу:

Если имя диапазона указано в ячейке («F2» в приведенном ниже примере указан как “Матем”), вы можете использовать ссылку на ячейку прямо в формуле. В следующем примере показано, как вычислять среднее значение с использованием именных диапазонов.

Пример 6. Создаем зависимый выпадающий список с помощью INDIRECT (ДВССЫЛ)

C помощью этой функции вы можете создавать зависимый выпадающий список.

Например, предположим, что у вас есть две колонки с названиями «Россия» и «США», в строках указаны города этих стран, как указано на примере ниже:

Для того, чтобы создать зависимый выпадающий список вам нужно создать два именованных диапазона для ячеек «A2:A5» с именем “Россия” и для ячеек «B2:B5» с названием “США”.

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

Теперь, если вы выберите в первом выпадающем списке, например, страну «Россия», то во втором выпадающем списке появятся только те города, которые относятся к этой стране. Такая же ситуация, если вы выберите страну «США» из первого выпадающего списка.

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе «От новичка до мастера Excel«. Успей зарегистрироваться по ссылке!

[expert_bq id=»1570″]Однако начинать лучше всего с простого, поскольку, не работая ранее с программой, далеко не все могут сразу понять, что делать для получения желаемого результата. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Электронные таблицы помогают выполнять огромное количество разнообразных вычислений и других операций. Однако начинать лучше всего с простого, поскольку, не работая ранее с программой, далеко не все могут сразу понять, что делать для получения желаемого результата. Рассмотрим подробно и поэтапно, как сделать формулу в Excel:
Формулы в Excel: сложение, подсчет количества строк, поиск и подстановка значений из одной таблицы в другую
- Функция INDIRECT (ДВССЫЛ) это волатильная функция (используйте с осторожностью);
- Она пересчитывает значения каждый раз, когда вы открываете Excel файл, и каждый раз когда вычисление запускается на рабочем листе Excel;
- Так как волатильные функции постоянно обновляются и производят вычисления, это, в свою очередь, замедляет работу вашего Excel файла.
- Аргумент текстовой ссылки может выглядеть как:
  — ссылка на ячейку, которая содержит ссылку на ячейку в формате «A1» или «R1C1».
  — ссылка на ячейку в двойных кавычках.
  — именованный диапазон, возвращающий ссылку
Посмотрите на рисунок ниже. В ячейке А1 написан текст D4, а в самой ячейке D4 указано число 9999. Если мы напишем формулу ДВССЫЛ(A1), то в результате получим число 9999. То же самое мы получим если напишем формулу так:
=ДВССЫЛ(«D4»)
То есть мы написали текстом адрес ячейки D4 и функция ДВССЫЛ вернула нам то значение, которое находится по данному адресу (D4).

Дополнительные данные → Похожие темы → Все про Exel → Как вставить значения → Как объединить ячейки → Как вставить форматы → Дополнительные данные → Вставить формулы→ Аргументы функции