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

Обновлено: 11.05.2024

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

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

Так что пользуйтесь на здоровье в любых целях! Главное, чтобы Вам это пригодилось! А лист с зарплатой показывать не буду :) Но поверьте он есть!

Разберем простой пример.

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

1. Создадим новый лист (Лист5 в моем случае). Кто не знает как это делать - нажать на плюс возле последнего листа в вашем документе. Для удобства напишем какие данные будем выводить:

2. Чтобы получить значение заданной ячейки из листа 4, нужно выбрать любую ячейку, поставить знак " = ", внизу нажать на лист4 и выбрать нужную ячейку.

Я решил немного усложнить задачу и не просто выбрать одну ячейку, а найти сумму значений нескольких ячеек. Для этого необходимо в пустой ячейке начать писать " =СУММ( " и уже после этого перейти на лист4 :

3. Попадаем на лист 4, находим нужный массив чисел и выделяем его. Вверху автоматически формируется формула:

Разберем формулу для тех кто не понял:

Знак равно означает начало функции. Так программа понимает, что необходимо будет выполнить какое-то действие.
Слово " СУММ " означает, что необходимо будет выполнить сложение для выражения, указанного в круглых скобках.
Слово " Лист4! " означает, что данные будут браться именно с Листа 4, ведь на других листах есть ячейки с такими же названиями.
Слова " C21:I21 " - это диапазон значений, то есть программа начнет с первой указанной ячейки и будет повторять действие до последней указанной ячейки.

Нажимаем " Enter " и программа переносит нас к результату:

Теперь мы знаем сумму данных за указанный период (1941г). Аналогичным образом я сделал для 1944 года:

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

Ячейки- ячейки, на которые ссылается формула в другой ячейке. Например, если ячейка D10 содержит формулу =B5,ячейка B5 является влияемой на ячейку D10.

Зависимые ячейки — это ячейки, содержащие формулы, которые ссылаются на другие ячейки. Например, если ячейка D10 содержит формулу =B5, ячейка D10 является зависимой от ячейки B5.

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

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

Щелкните Файл > параметры > Дополнительные параметры.

Примечание: Если вы используете Excel 2007; нажмите кнопку Microsoft Office , Excel параметры, а затем выберите категорию Дополнительные параметры.

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

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

Выполните одно из указанных ниже действий.

Трассировка ячеек, обеспечивающих формулу данными (влияющих ячеек)

Укажите ячейку, содержащую формулу, для которой следует найти влияющие ячейки.

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

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

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

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

Трассировка формул, ссылающихся на конкретную ячейку (зависимых ячеек)

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

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

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

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

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

В пустой ячейке введите = (знак равно).

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

Чтобы удалить все стрелки трассировки, на вкладке Формулы в группе Зависимости формул нажмите кнопку Удалить стрелки .

Проблема: Microsoft Excel издает звуковой сигнал при выборе команды Зависимые ячейки или Влияющие ячейки.

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

Ссылки на текстовые поля, внедренные диаграммы или рисунки на таблицах.

Отчеты для отчетов в отчетах.

Ссылки на именуемые константы.

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

Чтобы увидеть выделение цветом влияющих ячеек для аргументов формулы, выделите ячейку и нажмите клавишу F2.

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

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

В данной статье, на простых примерах, описаны варианты использования функции ВПР MS Excel. Важные аспекты и возможные ошибки, которые возникают при использование данной функции. Функция ВПР в Excel.

Как вызвать функцию ВПР. Функция ВПР в Excel

В первую очередь разберемся, как вызвать данную функцию. Выбираем закладку Формулы. Находим кнопку Вставить функцию. И нажимаем ее. Так же, можно вызвать функцию ВПР, сочетанием клавиш Shift + F3.

Появляется диалоговое окно Вставка функции. В строке Поиск функции вводим ВПР. Нажимаем найти. По результатам поиска, в пункте Выберите функцию, появляется ВПР. Нажимаем на нее левой кнопкой мыши два раза или нажимаем ОК. Появляется непосредственно диалоговое окно функции ВПР – Аргументы функции.

Теперь перейдем непосредственно к вариантам применения функции ВПР.

Первый вариант использования функции ВПР.

Для примера возьмем две таблице. В одной Таблице №1 будет перечень с названиями конфет и будет указана их цена за кг. В другой, Таблица №2, тот же перечень, но с указанием их количества в кг. Наша задача добавить в Таблицу №2, в столбец Цена, цену конфет из Таблицы №1, чтобы в итоге получить стоимость. Названия конфет в разных таблицах находятся в разных местах, поэтому просто скопировать цену конфет с одной таблице в другую не получиться.

Перед тем, как вызвать функцию ВПР, выбираем нужную нам ячейку, в которой будет находиться наша формула функции и соответственно значение, которое мы хотим увидеть. В нашем случае это ячейка G3. Эта ячейка находиться в столбце Цена, Таблица №2. Функция ВПР позволит взять из Таблицы №1 цену Конфеты А и вставить эту цену в столбец Цена, Таблицы №2, напротив Конфеты А.

Вызываем функцию ВПР, как описано выше.

Аргументы функции. Функция ВПР в Excel.

Искомое_значение.

Значение поиска, которое должно быть найдена в указанном нами диапазоне, в строке Таблица. В нашем примере мы указываем Конфеты Ж (ячейка Е3, Таблица №2). Так как это значение идет первое в столбце Название конфет, Таблица №2. (Это не принципиально, но удобно). Это значение, которое будет искать наша функция в Таблице №1.

Что бы выбрать нужную нам ячейку с значением, достаточно просто стать курсором в строку Искомое_значение, а потом клацнуть левой кнопкой мыши, по нужной ячейке в таблице ( В нашем примере ячейка Е3).

Таблица.

Можно присвоить нашему диапазону имя, и прописать его в строке Таблица.

Как это сделать. Выбираем нужный нам диапазон. Таблица №1. Выбираем закладку Формулы, кнопка Задать имя. Нажимаем. Появляется диалоговое окно Создание имени. Пишем любое имя. Но нужно его запомнить. Например Конфеты. Нажимаем ОК.

В строке Таблица, вместо диапазона нужно будет ввести имя, которое мы присвоили – Конфеты

Номер_столбца.

Функция ВПР осуществляет поиск значение в самом левом столбце таблицы указанного диапазона поиска. Функция присваивает этому столбцу номер 1, по умолчанию. В нашем примере самый левый столбце, это Название конфет в Таблице №1. А в строке Номер_столбца, нам нужно указать, какой номер по порядку имеет столбец, из которого нужно перенести данные. В нашем примере это столбце Цена в Таблице №1. Он «второй» по порядку, если считать слева на право, от столбца Название конфет, Таблица №1. Поэтому в строку Номер_столбца мы пишем цифру 2. Если бы столбец Цена, Таблица №1 был бы расположен по порядку не на втором месте, а предположим на десятом, то мы соответственно указывали бы в строке Номер_столбца цифру 10.

Интервальный _просмотр.

В этой строке мы пишем цифру ноль «0». Это значит, что функция ВПР будет осуществлять поиск точных совпадений между значениями поиска (Искомое_значение) и значениями в крайнем левом столбце диапазона поиска (Таблица). В нашем примере поиск точных совпадений будет происходить между столбцом Название конфет, Таблица №1, и столбцом Название конфет в Таблице №2.

Если мы поставим цифру один «1», функция будет осуществлять поиск не точного совпадения, а приближенного к нашему критерию поиска.

Вот как это выглядит все вместе.

Протягиваем формулу по всему столбцу Цена в Таблице №2. Все цены перенесены с Таблице №1 в Таблицу №2.

Второй вариант использования функции ВПР.

У нас есть Таблица №1 и Таблица №2. Каждая таблица состоит из одного столбца. Для понимания алгоритма работы функции ВПР, в данном случае, таких простых таблиц достаточно. Столбцы содержат практически одинаковые данные. При этом, нам нужно сравнить их и узнать, какие данные есть в Таблице №2, но нет в Таблице №1.

Справа от Таблицы 2, в ячейку G3, вставляем функцию ВПР. Это расположение взято в качестве примера, можно использовать любой другой столбец и оформление.

В диалоговом окне, Аргументы функции прописываем следующие данные:

Искомое_значение. Это значение ячейки из Таблицы №2, наличие которой мы проверяем в Таблице №1. В нашем примере, это ячейка F3 (Значение 9).

Таблица. В данном случае мы указываем не диапазон всей таблицы, а только диапазон конкретного столбца, который мы сравниваем. Можно выделять столбец в таблице. А можно выделять весь столбец листа. В том случае, если в нем больше нет других данных. Вместо диапазона можно указать заданное имя столбца (Задаем имя).

Номер_столбца. Поскольку в таблице указан конкретный столбец, то здесь мы ставим цифру 1, так как искомое значение и данные, которые мы хотим перенести совпадают.

Интервальный _просмотр. Здесь ставим ноль «0», так как хотим, чтобы функция искала точные совпадения.

Можно проверить с точностью наоборот. И найти какие данные есть в Таблице №1 но нет в Таблице № 2.

Обратите внимание. Функция ВПР в Excel.

Функция ВПР осуществляет поиск значений (это значения, которые указаны в строке Искомое_значение) в первом (самом левом) столбец таблицы, диапазон которой указан в строке Таблица.

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

Если в диапазоне Таблица указан один, конкретный столбец, то функция ВПР проверяет только его. И данное правило не обязательно.

Можно осуществлять поиск на разных Листах. Алгоритм работы такой же. Формула функции будет выгладить вот так: =ВПР(E6;Лист1!$B$1:$C$11;2;0). В нашем примере формулы функция ВПР расположена на Листе 2, а поиск значения и перенос данных с диапазона поиска происходит на Листе 1. Вместо диапазона можно использовать Заданное имя. Например Конфеты. Тогда формула функции будет выглядеть вот так: =ВПР(E6;Конфеты;2;0).

Возможные ошибки.

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

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\[Отчет.xlsx]Лист1'!B2.

Описание элементов ссылки на другую книгу Excel:

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

Данную ссылку следует читать так:

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

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

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