Как взять значение из другого листа в Excel?

Ссылка на лист, отделенная от ссылки на диапазон значений.

1. Щелкните ячейку, в которую нужно ввести формулу.
2. В строка формул введите = (знак равенства) и формулу, которую нужно использовать.
3. Щелкните ярлычок листа, на который нужно сослаться.
4. Выделите ячейку или диапазон ячеек, на которые нужно сослаться.

Как связать данные расположенные на разных листах Excel?

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

Как перенести данные из одного листа в другой Excel?

1. Щелкните ярлычок листа правой кнопкой мыши и выберите команду Переместить или скопировать.
2. Установите флажок Создать копию.
3. В разделе перед листом укажите, куда вы хотите поместить копию.
4. Нажмите кнопку ОК.

Как в Excel получить данные из другого файла?

1. На вкладке Данные щелкните Получить данные > Из файла > Из книги . …
2. Найдите книгу в окне Импорт данных .
3. В окне Навигатор выберите таблицу или лист, которые вы хотите импортировать, а затем нажмите кнопку Загрузить или Изменить .

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

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

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

1. Найдите ячейку, на которую хотите сослаться, и запомните, где она находится. …
2. Перейдите к нужному листу. …
3. Выбранный лист откроется.
4. Найдите и выделите ячейку, в которой должно появиться значение. …
5. Введите знак равенства (=), название листа с восклицательным знаком(!)

Как ссылаться на данные с другого листа Гугл таблицы?

Выберите или создайте таблицу. Выделите ячейку. Введите =, затем название листа, восклицательный знак и номер копируемой ячейки, например =Sheet1! A1 или =’Sheet number two’!

Как связать между собой два документа Excel?

Как объединить данные из листов c одинаковым именем на одном сводном листе

1. Нажмите кнопку Объединить листы …
2. Отметьте флажком Таблицы с заголовками если это так. …
3. Выберите листы для объединения, установив соответствующие флажки в дереве данных. …
4. Нажмите кнопку Объединить

Как в Excel взять данные с другого листа?

Необходимо открыть вкладку «Power Query». В разделе «Данные Excel» нажимаем кнопку (пиктограмму) «Из таблицы». Далее нужно выбрать диапазон ячеек, из которых нужно «притянуть» информацию и нажимаем «Ок». После выбора области данных появится окно настройки вида новой таблицы.

Как сделать несколько копий одного листа в Excel?

Перейти на вкладку VBA-Excel на ленте. Запустить Диспетчер листов.
…
Запустить Диспетчер листов.

1. Выбрать листы, которые необходимо скопировать (можно выбирать более 1 листа).
2. Нажать команду Копировать листы.
3. В открывшемся диалоговом окне выберите место в которое необходимо скопировать листы и их количество.

Как подтянуть данные из одной таблицы в другую Гугл таблицы?

1. Откройте файл в Таблицах.
2. В пустой ячейке введите =IMPORTRANGE.
3. Укажите в скобках следующие параметры через запятую, заключив их в кавычки: URL таблицы в Таблицах; название листа (необязательно) и диапазон импортируемых ячеек.
4. Нажмите Ввод.
5. Нажмите Открыть доступ, чтобы связать таблицы.

Как связать две таблицы в Excel?

В окне Управление связями нажмите кнопку Создать. В окне Создание связи щелкните стрелку рядом с полем Таблица и выберите таблицу из раскрывающегося списка. В связи «один ко многим» эта таблица должна быть частью с несколькими элементами.

Такая запись возможна, т.к. все листы имеют однотипные названия: Лист1, Лист2, Лист3 и Лист4, и все таблицы на этих листах имеют одинаковую структуру (одинаковое количество строк и столбцов, наименования товаров, также должны совпадать).

Копирование и вставка

• Чтобы сделать совсем красиво, вы можете изменить значок вставляемого файла.
• Такие «комплекты файлов» удобно создавать для рассылки нескольким адресатам. Получатель скачивает файл и хранит все файлы в одном. Удобно
• Если же сделать изменения во вложенном файле и сохранить основной документ, то все правки останутся и будут хранится в основном документе.

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

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

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

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

Скопируйте приведенную ниже таблицу и вставьте его в Excel в ячейке A1. Обязательно выберите диапазон ячеек E2: E11, введите формулу = C2: C11 * D2: D11, и нажмите клавиши Ctrl + Shift + Ввод, чтобы упростить формулы массива.

В образце книги выберите ячейки от E2 до E11. В них будут помещены результаты.

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

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

Введите формулу, используя один из методов из шага 2 предыдущего примера:

Формула умножает значения в диапазонах ячеек C2:C11 и D2:D11, а затем складывает результаты, получая общую сумму.

Перемещение формулы — Excel

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

Щелкните ячейку с формулой, которую нужно переместить.

Выберите Главная > Вырезать или нажмите клавиши CTRL+X.

Выберите ячейку, в которую нужно вставить формулу, и щелкните Вставить (или нажмите клавиши CTRL+V).

Совет: Вы можете также контекстного меню Вырезать и Вставить формулу.

Копирование и вставка формулы в другую ячейку или на другой лист в Excel 2016 для Mac

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

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

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

Чтобы быстро вставить формулу вместе с форматированием, нажмите +V. Кроме того, можно щелкнуть стрелку рядом с кнопкой Вставить:

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

Формулы — Вставка только формул и без форматирования исходной ячейки.

Формулы и форматы чисел — Вставка только формул и форматов чисел (например: процентный формат, формат валюты и т. д.).

Сохранить исходное форматирование — Чтобы вставить формулу, номера форматирования, шрифт, размер шрифта, границы и заливки исходной ячейки.

Вставить значения — для исключения формулу и вставить их результатами.

Другие доступные параметры, которые могут пригодиться:

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

Сохранить ширину столбцов оригинала — Чтобы вставить формулу, форматирование чисел, шрифта, размер шрифта, заливки, границы и ширины исходной ячейки.

Транспонировать — Используйте этот параметр при копировании нескольких ячеек. При копировании ячейки, которые находятся рядом друг с другом в строку, этот вариант будет вставьте их в столбце. Если ячейки в столбце, он будет вставлен в рядом в строке. Этот параметр Вставка формулы, числовой формат, шрифт, размер шрифта, заливки, границы.

Проверка и исправление ссылок на ячейки в новом месте

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

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

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

В строке формул выделите ссылку, которую нужно изменить.

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

Перенос формулы в другое место

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

Щелкните ячейку с формулой, которую хотите перенести.

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

Чтобы вставить формулу с сохранением форматирования, на вкладке Главная выберите команду Вставить или нажмите клавишу + V.

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

Сохранить исходное форматирование: вставка только формул, форматов чисел, атрибутов шрифта и его размера, границ и заливки исходной ячейки.

Значения и исходное форматирование Вставка только форматирования и значения. Формула, будут исключены.

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

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

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

Выберите пункты Главная > Копировать или нажмите клавиши CTRL+C.

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

Чтобы вставить формулу с сохранением форматирования, выберите пункты Главная > Вставить или нажмите клавиши CTRL+V.

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

Проверьте ссылки на ячейки для нового расположения.

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

Также можно использовать сочетание клавиш Ctrl + X, чтобы переместить формулу.

Использование функции автозаполнения при вводе формул

Включение функции автозаполнения

В разделе формулы и списки щелкните Автозаполнение.

Установите флажок Показать Автозаполнение параметров функции и именованные диапазоны.

Определить, когда нужно отобразить меню автозаполнения

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

В разделе формулы и списки щелкните Автозаполнение.

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

Вставка элемента из меню автозаполнения

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

Можно также нажать кнопку ВВОД для вставки элемента из меню «Автозаполнение» в Excel для Mac. С помощью клавиши TAB рекомендуется на совместимость с версиями Excel для Windows.

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

Включение функции автозаполнения

В разделе формулы и списки щелкните Автозаполнение.

Установите флажок Показать меню автозаполнения для функций, именованные диапазоны.

Определить, когда нужно отобразить меню автозаполнения

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

В разделе формулы и списки щелкните Автозаполнение.

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

Совет: Можно также отобразить меню автозаполнения, нажав клавиши CONTROL + OPTION + стрелка вниз.

Вставка элемента из меню автозаполнения

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

Можно также нажать кнопку ВВОД для вставки элемента из меню «Автозаполнение» в Excel для Mac. С помощью клавиши TAB рекомендуется на совместимость с версиями Excel для Windows.

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

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

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

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

Выберите Главная > Копировать или нажмите клавиши CTRL+C.

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

Чтобы вставить формулу с сохранением форматирования, выберите пункты Главная > Вставить или нажмите клавиши CTRL+V.

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

Формулы Чтобы вставить только формулу .

Значения Чтобы вставить значения только (результат вычисления по формуле) .

Проверка и исправление ссылок на ячейки в новом месте

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

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

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

В формуле в строке , выделите ссылку, вы хотите изменить.

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

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

Перенос формулы в другое место

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

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

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

Выберите Главная > Вырезать или нажмите клавиши Ctrl + X.

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

Чтобы вставить формулу с сохранением форматирования, выберите пункты Главная > Вставить или нажмите клавиши CTRL+V.

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

Формулы Чтобы вставить только формулу .

Значения , чтобы вставить результат формулы только (результат вычисления по формуле).

Замена формулы на ее результат

Если формула вводится как формула массива, выделите диапазон, содержащий формулу массива.

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

Внимание: Формулы в Excel, вместо которых вводится их значение, удаляются без возможности восстановления. Чтобы восстановить формулу после ее случайной замены, сразу после ввода или вставки значения нажмите на панели инструментов Стандартная кнопку Отменить . Перед заменой формулы ее результатом рекомендуется создать резервную копию книги.

Если формула вводится как формула массива, выделите диапазон, содержащий формулу массива.

На панели инструментов Стандартная нажмите кнопку Копировать .

На панели инструментов Стандартная нажмите кнопку Вставить .

Щелкните стрелку рядом с элементом Параметры вставки и выберите пункт Только значения.

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

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

Как взять значение из другого листа в Excel? Ваша онлайн энциклопедия

1. Щелкните ярлычок листа правой кнопкой мыши и выберите команду Переместить или скопировать.
2. Установите флажок Создать копию.
3. В разделе перед листом укажите, куда вы хотите поместить копию.
4. Нажмите кнопку ОК.

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

Excel двссыл на другую книгу

Синтаксис функции

Второй аргумент а1 — это логическое значение (ИСТИНА или ЛОЖЬ), указывающее, какого типа ссылка содержится в аргументе Ссылка_на_ячейку .

• Если a1 имеет значение ИСТИНА или опущена, то ссылка_на_ячейку интерпретируется как ссылка в стиле A1.
• Если a1 имеет значение ЛОЖЬ, то ссылка_на_ячейку интерпретируется как ссылка в стиле R1C1.

Если ссылка_на_ячейку не является допустимой ссылкой, то функция ДВССЫЛ() возвращает значение ошибки #ССЫЛКА!

Рассмотрим несколько задач

Пусть на листах Лист1, Лист2, Лист3 и Лист4 в одних и тех же ячейках находятся однотипные данные (Продажи товаров за квартал) См. файл примера .

Сформируем итоговую таблицу Продажи за год на другом листе. В этой таблице будут присутствовать данные с 4-х листов.

Для удобства в строке 9 на листе, где будет итоговая таблица, пронумеруем столбцы С, D, E, F как 1, 2, 3, 4 в соответствии с номером квартала и пронумеруем строки таблицы (см. столбец А).

Чтобы вывести данные с других листов используем формулу =ДВССЫЛ(«Лист»&C$9&»!B»&$A10+3)

Такая запись возможна, т.к. все листы имеют однотипные названия: Лист1, Лист2, Лист3 и Лист4, и все таблицы на этих листах имеют одинаковую структуру (одинаковое количество строк и столбцов, наименования товаров, также должны совпадать).

Примечание: Обратите внимание на использование в формуле смешанных ссылок ( C$9 и $A12).

Задача2 — ссылки на четные/ нечетные строки

C помощью ДВССЫЛ() можно вывести только четные или нечетные строки из исходной таблицы. В качестве исходной используем предыдущую таблицу Продажи за год.

Записав формулу =ДВССЫЛ(СИМВОЛ(65+H$26)&$A12*2+11) и скопировав ее в нужное количество ячеек, получим только четные записи из исходной таблицы. Формула в ячейке H12 эквивалентна =ДВССЫЛ(«B13»)

Примечание: С помощью функции СИМВОЛ() можно вывести любой символ, зная его код. =СИМВОЛ(65) выведет букву А (английскую), =СИМВОЛ(66) выведет В, =СИМВОЛ(68) выведет D.

C помощью формулы =ДВССЫЛ(СИМВОЛ(65+N$26)&$A12*2+10) можно вывести только нечетные строки, а с помощью формулы =ДВССЫЛ(СИМВОЛ(65+B$26)&$A28+11) вообще произвольные строки, номера которых заданы в столбце А.

Задача3 — транспонирование таблиц/ векторов

С помощью нижеуказанной формулы можно транспонировать исходную таблицу (подробнее читайте здесь):

О транспонировании таблиц можно прочитать в этом разделе.

Примечание: О других применениях функции ДВССЫЛ() можно прочитать в статьях, список которых расположен ниже.

Имена Имя1 и Имя4 — это именованные диапазоны, т.е. эти имена возвращают ссылки.

Имя Имя2 — это константа массива, т.е. массив чисел, а не ссылка.

Также массив значений будет возвращать функция СМЕЩ() . см. Имя5.

Имя Имя3 — это именованная формула, которая возвращает число, а не ссылку.

Создадим табличку, в которой укажем эти имена. Постараемся найти сумму значений, которые вернут эти имена, использовав формулу =СУММ(ДВССЫЛ(A2)) .

Как видим, работают только те формулы, которые ссылаются на ячейки содержащие Имя1 и Имя4. Только эти имена ссылаются на диапазоны ячеек. Если вспомним синтаксис функции ДВССЫЛ() , то в качестве первого аргумента можно использовать » текстовую строку в формате ссылки», а не числовые массивы.

Формула =СУММ(ДВССЫЛ(A2)) эквивалентна =СУММ(ДВССЫЛ(«имя1»)) Вместо «имя1» подставляется ссылка =Имена!$A$14:$A$17 ( текстовая строка в формате ссылки ), которая успешно разрешается функцией ДВССЫЛ() . В итоге функция ДВССЫЛ() возвращает массив из диапазона $A$14:$A$17 , который затем суммируется.

В случае с Имя2 все по-другому. Формула =СУММ(ДВССЫЛ(A3)) эквивалентна =СУММ(ДВССЫЛ(«имя2»)) Вместо «имя2» подставляется массив , который не является текстовой строкой и не может быть обработан функцией ДВССЫЛ() . Поэтому она возвращает ошибку.
Аналогичный результат получим для имен: Имя3 и Имя5.

В чем разница между =СУММ(ДВССЫЛ(имя5)) и =СУММ(ДВССЫЛ(«имя5»)) ? Когда мы записываем =СУММ(ДВССЫЛ(«имя5»)) мы говорим функции ДВССЫЛ() работать с имя5 как с адресом. Это сработает, если имя5 содержит » Имена!$A$14:$A$17″ или что-то в этом роде. Но, имя5 указывает на формулу, которая возвращает значения из диапазона Имена!$A$14:$A$17. Т.к. это не ссылка, то функция вернет ошибку.

Функция ДВССЫЛ возвращает ссылку, которая задана текстовой строкой. К примеру, формула = ДВССЫЛ (А3) аналогична формуле = А3. Но для этой функции ссылка является просто текстовой строкой: ее можно изменять формулами.

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

Синтаксис функции с описанием

Функция ДВССЫЛ в Excel: примеры

Начнем с хрестоматийного примера, чтобы понять принцип работы функции.

Рассмотрим практическое применение функции. На листах 1, 2, 3, 4 и 5 в одних и тех же ячейках расположены однотипные данные (информация об образовании сотрудников фирмы за последние 5 лет).

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

Пишем формулу в ячейке В4 и копируем ее на всю таблицу (вниз и вправо). Данные с пяти различных листов собираются в итоговую таблицу.

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

Чтобы получить только нечетные записи, используем формулу:

Примечание. Функция СИМВОЛ возвращает символ по заданному коду. Код 65 выводит английскую букву A. 66 – B. 67 – С.

Допустим, у пользователя имеется несколько источников данных (в нашем примере – несколько отчетов). Нужно вывести количество сотрудников, основываясь на двух критериях: «Год» и «Образование». Для поиска определенного значения в базе данных подходит функция ВПР.

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

Но ВПР информацию в таком виде не сможет переработать. Поэтому каждому отчету мы дали имя (создали именованные диапазоны). Отдельно сделали выпадающие списки: «Год», «Образование». В списке «Год» – названия именованных диапазонов.

Задача: при выборе года и образования в столбце «Количество» должно появляться число сотрудников.

Если мы используем только функцию ВПР, появится ошибка:

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

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

Функции ВПР и ДВССЫЛ в Excel

Теперь формула работает корректно. Для решения подобных задач нужно применять одновременно функции ВПР и ДВССЫЛ в Excel.

Предположим, нужно извлечь информацию в зависимости от заданного значения. То есть добиться динамической подстановки данных из разных таблиц. К примеру, указать количество сотрудников с незаконченным высшим образованием в 2015 и в 2016 году. Сделать так:

В отношении двух отчетов сработает комбинация функций ВПР и ЕСЛИ:

Но для наших пяти отчетов применять функцию ЕСЛИ нецелесообразно. Чтобы возвратить диапазон поиска, лучше использовать ДВССЫЛ:

• $A$12 – ссылка с образованием (можно выбирать из выпадающего списка);
• $C11 – ячейка, в которой содержится первая часть названия листа с отчетом (все листы переименованы: «2012_отчет», 2013_отчет» и т.д.);
• _отчет!A3:B10 – общая часть названия всех листов и диапазон с отчетом. Она соединяется со значением в ячейке С11 (&). В результате получается полное имя нужного диапазона.

Таким образом, эти две функции выполняют подобного рода задачи на отлично.

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

Применение формулы ДВССЫЛ

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

Данный оператор относится к категории функций «Ссылки и массивы» и имеет следующий синтаксис:

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

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

Но в отличие от выражения «=A1» оператор ДВССЫЛ привязывается не к конкретной ячейке, а к координатам элемента на листе.

Рассмотрим, что это означает на простейшем примере. В ячейках B8 и B9 соответственно размещена записанная через «=» формула и функция ДВССЫЛ. Обе формулы ссылаются на элемент B4 и выводят его содержимое на лист. Естественно это содержимое одинаковое.

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

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

Теперь давайте рассмотрим конкретные примеры применения оператора.

Пример 1: одиночное применение оператора

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

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

Происходит запуск окошка Мастера функций. Перемещаемся в категорию «Ссылки и массивы». Из перечня выбираем значение «ДВССЫЛ». Щелкаем по кнопке «OK».

В поле «A1», так как мы работает в обычном типе координат, можно поставить значение «ИСТИНА», а можно оставить его вообще пустым, что мы и сделаем. Это будут равнозначные действия.

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

Пример 2: использование оператора в комплексной формуле

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

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

Итак, чтобы присвоить имя первому элементу столбца, выделяем его и жмем правую кнопку мыши. Открывается контекстное меню. Выбираем в нем пункт «Присвоить имя…».

Запускается окно создания имени. В поле «Имя» вписываем наименование «Январь». Больше никаких изменений в окне производить не нужно, хотя на всякий случай можно проверить, чтобы координаты в поле «Диапазон» соответствовали адресу ячейки содержащей размер выручки за январь. После этого щелкаем по кнопке «OK».

Как видим, теперь при выделении данного элемента в окне имени отображается не её адрес, а то наименование, которое мы ей дали. Аналогичную операцию проделываем со всеми другими элементами столбца «Доход», присвоив им последовательно имена «Февраль», «Март», «Апрель» и т.д. до декабря включительно.

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

В активировавшемся окошке Мастера функций перемещаемся в категорию «Математические». Там выбираем наименование «СУММ». Щелкаем по кнопке «OK».

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

Запускается уже знакомое нам окошко Мастера функций. Перемещаемся в раздел «Ссылки и массивы» и выбираем там наименование оператора ДВССЫЛ. После этого действия щелкаем по кнопке «OK» в нижней части окошка.

После того, как адрес отобразился в поле, не спешим жать на кнопку «OK», так как это вложенная функция, и действия с ней отличаются от обычного алгоритма. Щелкаем по наименованию «СУММ» в строке формул.

Снова открывается окно аргументов оператора ДВССЫЛ. Заносим в поле «Ссылка на ячейку» адрес элемента на листе, где расположено наименования месяца, который завершает расчетный период. Опять координаты должны быть вписаны без кавычек. Поле «A1» снова оставляем пустым. После этого щелкаем по кнопке «OK».

Как видим, после данных действий программа производит расчет и выдает результат сложения дохода предприятия за указанный период (март — май) в предварительно выделенный элемент листа, в котором располагается сама формула.

После того, как адрес отобразился в поле, не спешим жать на кнопку «OK», так как это вложенная функция, и действия с ней отличаются от обычного алгоритма. Щелкаем по наименованию «СУММ» в строке формул.

Как создать в Excel выпадающий список с данными из другого файла — Сводные таблицы Excel 2010

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