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

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

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

Введите доход за январь, февраль и март на трех отдельных листах. Затем сложите их на четвертом листе ячейки B2.

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

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

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

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

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

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

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

Ссылка на лист в другой книге уже содержит 5 элементов. Это выглядит так: = ‘C: Docs Sheet1’! БИ 2.

Описания элементов, связанных с другой книгой Excel:

1. Путь к файлу книги (после отметки открывается апостроф).
2. Имя файла книги (имя файла в квадратных скобках).
3. Название листа этой книги (после названия апостроф закрывается).
4. Восклицательный знак.
5. Ссылка на ячейку или диапазон ячеек.

Эта ссылка должна выглядеть так:

• книга находится на диске C: в папке «Документы;
• имя файла книги «Отчет» с расширением «.xlsx»;
• «Лист1» в ячейке B2 содержит значение, на которое ссылается формула или функция.

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

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

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

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

Итак, у нас есть файл Excel с множеством листов. Давайте посмотрим на простой пример, когда имена листов за нас не изменились. То есть у нас есть Sheet1, Sheet2, Sheet3 и так далее.

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

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

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

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

Есть несколько способов решить эту проблему, например, используя простой макрос, который можно записать с помощью устройства записи макросов. Что ж, воспользуемся функцией КОСВЕННО для решения проблемы.

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

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

Взгляните на фото ниже. Ячейка A1 содержит текст D4, а сама ячейка D4 содержит число 9999. Если мы напишем косвенную формулу (A1), результатом будет число 9999. Мы получим то же самое, если напишем формулу следующим образом: 

= КОСВЕННО («D4») 

То есть мы записали адрес ячейки D4 в текст, и функция ДВССЫЛ вернула нам значение, найденное по этому адресу (D4).

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

= Sheet1! B2 (оборот магазина 1 в январе)

= Лист2! B2 (Войдите в магазин 2 января) и т.д. 

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

Если мы напишем формулу 

DIRECTIVE («Sheet1! B2»), DIRECTION («Sheet2! B2») и так далее, тогда функция вернет нам то же самое, но даже это не решает нашу проблему, потому что «Sheet1! B2» записывается открытым текстом и поэтому он не изменится, когда вы его потянете.

Чтобы решить нашу проблему, мы разделим текст «Sheet1! B2» на две части (отдельно «Sheet1» и отдельно «! B2» — обратите внимание на восклицательный знак во второй части текста), а затем склеим их вместе. Мы напишем текст Sheet1, Sheet2 и так далее перед соответствующими хранилищами, поскольку мы пишем Sheet1, мы можем просто перетащить ячейку вниз и автоматически получить список Sheet1, Sheet2 и так далее.

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

= КОСВЕННО (LA2 ​​& «! B2»)

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

= КОСВЕННО (A2 & «! B3») и опустите вниз, аналогично марту.

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

Итак, мы решили задачу, как быстро заполнить данные за определенный месяц, но нам нужно заполнить данные за оставшуюся часть месяца. Мы знаем, что данные за январь соответствуют ячейке B2, февраль — B3, март — B4. То есть буква «В» остается неизменной, меняется только цифра. Зная это, мы просто разделим текст внутри КОСВЕННОЙ ссылки не на 2 части, а на три части. Добавьте еще одну строку над таблицей и напишите соответствующую ячейку месяца над числами. Напишем формулу.

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

= КОСВЕННО ($ A3 & «! B» & C $ 1)

которые можно растянуть вправо и в низ.

Связанные примечания:

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

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

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

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

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

Чтобы создать простую формулу, следуйте инструкциям ниже:

1. Сделайте любую ячейку активной. Щелкните строку ввода формулы. Поставьте знак равенства.

1. Введите любое выражение. Может использоваться как числа

и ссылки на ячейки.

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

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

Возьмем для примера следующее выражение.

Он состоит из:

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

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

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

• скобки;
• экспоненты;
• умножение и деление (в зависимости от последовательности);
• сложение и вычитание (тоже в зависимости от последовательности).

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

Это включает:

• сложение — «+» (плюс);

= 2 + 2

• отрицание или вычитание — «-» (минус);

= 2-2 = -2

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

• умножение – «*»;

= 2 * 2

• разделение «/»;

= 2/2

• процентов «%»;

= 20%

• экспонента – «^».

= 2 ^ 2

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

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

• знак «равно» – «=»;

= C1 = D1

• знак «больше» – «>»;

= C1> D1

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

    = СУММЕСЛИ (Si3: SI9; SI3: SI9; «> 2»; SI3: SI9; «3″)

    1. Результат всех формул будет следующим.

    

    Математические функции и графики

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

    Например, попробуем построить графики для показателя степени и некоторого уравнения. Инструкция будет следующей:

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

    

    1. Чтобы преобразовать значение «X», вам необходимо указать следующие формулы.

    = ESP (B4) = B4 + 5 * B4 ^ 3/2

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

    

    1. Выделите всю таблицу. Перейдите на вкладку «Вставка». Щелкните инструмент «Рекомендуемые диаграммы».

    

    1. Выберите тип «Линия». Чтобы продолжить, нажмите «ОК».

    

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

    

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

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

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

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

    В остальном все выглядит более-менее одинаково. Например, давайте посчитаем сумму нескольких ячеек. Это требует:

    1. Укажите некоторые данные для расчета. Щелкните любую ячейку. Щелкните значок «Fx».
    1. Выберем категорию «Математика». Найдите функцию «СУММ» и нажмите «ОК».
    1. Указываем данные в необходимом диапазоне. Чтобы увидеть результат, нужно нажать «ОК».
    1. Можно попробовать пересчитать в любом другом редакторе. Процесс будет происходить точно так же.

    Заключение

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

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

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

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

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