Видео: Как убрать ноль при вычислениях в формулах Excel
Работа с формулами Excel предполагает не только написать формулу, но и заменить ссылки в формулах, при необходимости.
Чтобы поменять в формулах Excel ссылки на другой лист, нужно воспользоваться функцией «Найти и выделить».
Допустим, мы копируем лист Excel (лист 1) с таблицей, в которой есть формулы со ссылкой на «лист 2». Нам надо поменять в скопированной таблице в формулах ссылку «лист 2» на ссылку «лист 3».
Удобнее здесь выделить диапазон и заменить разом ссылку во всех нужных нам формулах. Делают это с помощью макросов.
Но мы применим другой вариант, немного трудоемкий, но все равно легче и быстрее, чем вручную писать ссылку в каждой формуле. Делаем замену ссылки функцией «Найти и выделить», расположенной на закладке «Главная».
Но, здесь есть нюансы, поэтому рассмотрим подробно на примере. В примере все таблицы придуманы для этого примера, поэтому не ищите в них бухгалтерскую логику. Итак, есть первый лист «январь», в нем, список сотрудников и их доход.
Второй лист «касса январь», в нем — суммы по сотрудникам.
Третий лист «Итого», в нем таблица «итого январь».
В таблицу «итого январь» переносится по формуле из листа «январь» итоговая сумма по каждому сотруднику и из листа «касса январь» переносится сумма по каждому сотруднику. Получается, в таблице «итого январь» на странице «Итого» стоят в формулах ссылки на страницы «январь» и «касса январь».
Смотрите строку формул. Здесь стоит абсолютная ссылка, но может стоять и относительная. Есть еще такие же два листа, но: «февраль» и «касса февраль».
Для этих таблиц мы на листе «Итого» копируем таблицу «итого январь» и назовем ее «итого февраль». В этой, скопированной таблице «итого февраль» на листе «Итого», нам надо изменить ссылки с январских листов на февральские листы.
- В таблице, в которой будем менять ссылки, выделяем нужные ячейки (диапазон ячеек). Диапазон ячеек быстрее и удобнее выбрать по имени диапазона, особенно, если ячейки не смежные.
- На закладке «Главная» в разделе «Редактирование» нажимаем кнопку «Найти и выделить». В строке «Найти» пишем ссылку, которую будем менять.
Нажимаем кнопку «Найти все». Вышел список ячеек с формулами с ссылкой «январь!». Нюанс! Благодаря тому, что мы предварительно отметили диапазон с нужными нам ячейками, в списке будут показаны ячейки только из отмеченного диапазона.
Если бы не выделяли предварительно диапазон, то в списке были бы ячейки со всего листа. В т.ч., и за январь, где нам не надо менять ссылки. Это бы затруднило нашу работу.
Нажимаем вкладку «Заменить» и в строке «Заменить на» пишем новую ссылку, которую нам надо. А вот теперь придется поработать над каждой ячейкой. Наводим курсор на каждую строку списка ячеек и затем нажимаем кнопку «Заменить».
Если нужно поменять ссылку в формуле Excel на всем листе, то нажмем кнопку «Заменить все», и заменятся ссылки во всех формулах листа, даже в тех, которых нет в списке. Можно настроить список для удобной ориентировки в нем – это кнопка «Параметры».
Здесь можно настроить просмотр списка по строкам или по столбцам, искать формулы на этом листе или в книге. Всё, ссылки в формулах заменены. Также меняем ссылки «`касса январь`!» на ссылку «`касса февраль`!».
В этом названии ссылки нужно указывать и одинарные кавычки, писать строго, как написано в формуле.
В таблице Excel можно закрепить верхние строки (шапку таблицы) и левые столбцы в таблице. И, тогда, при прокрутке таблицы вниз или в сторону, эти строки и столбцы всегда будут видны на экране монитора. Так удобнее работать с большой таблицей.
Всем добрый день, прошу Вашей помощи.
Ситуация (сразу буду описывать на прикрепленном примере):
1. В книге есть три листа r1, r2, r3, содержащие данные для расчета.
2. На основании этих листов проводятся некоторые вычисления в листах 1.1, 2.1, 3.1.
3. Вычисления и оформление на этих листах одинаковы, с точностью до наименования листа, с которого они берут данные. Т.е. лист 1.1 расчитывается на основании листа r1, лист 2.1 — на основании r2, … .
Дополнения (о том, наскольлко сложен реальный мир):
1. Листов, что расчетных, что расчитываемых больше.
2. Формул на каждом листе много, они, местами, очень большие.
3. Каждый лист на самом деле использует данные не одного расчетного листа а нескольких.
Проблема:
Иногда принцип расчета какой-то конкретной ячейки на расчетных листах может меняться, требуется найти/создать наиболее удобный механизм корректировки формул на всех листах.
Рассмотренные варианты:
1. Меняем все на одном листе, к примеру, первом. Копируем его поверх остальных. Через замену (Ctrl+H) меняем на странице ссылки с ‘r1’ на нужный индекс, можно записать макрос.
Почему данный вариант не подходит: ручная замена может привести к ошибкам, макрос нужно затачивать под каждый конкретный лист или группу однотипных листов + переименование листа точно приведет к ошибкам.
2. Использование ДВССЫЛ. Где-то в шапке или в боттоме формируем карту ссылок, в которой расписываем какие листы являются основой для текущего. Составля формулы пишем их через ДВССЫЛ. Поменяли что-то на одном листе, спокойно копируем на другие.
Почему данный вариант не подходит: использование ДВССЫЛ сильно загромождает формулу и сильно мешает ее осмыслению, анализу, корректировкам. Если формула и так занимает много-много строк, то разбавление каждой ссылки ДВССЫЛом делает ее вообще нечитаемой.
К чему пришел я?
Долго думал, как побороть свою проблему, единственное что придумал: создание собственной пользовательской функции, которой даешь указатель на ячейку с формулой образцом и говоришь, какие названия листов в этой формуле и на что нужно заменить, а потом присвоить формулу-результат текущей ячейке.
Т.е., к примеру лист 3.1, ячейка А1:
=Функциякорректировки(‘Шаблон’!A1;»r1″;»r3″). На входе была формула =(‘r1′!B1+’r1′!B3)/’r1’!B2, получили =(‘r3′!B1+’r3′!B3)/’r3’!B2.
Более того, просто не придумал как это написать, в идеале функция должна выглядеть так:
=Функциякорректировки(‘Шаблон’!A1;ДиапазонПравилКорректировки).
ДиапазонПравилКорректировки — область шириной в две ячейки и произвольной длины. В левой ячейке каждой строки содержится искомое имя листа, в правой — то на которое нужно заменить.
Что примерно должна делать функция я понимаю, но знания VB явно нехватает для написания это штуки, представляю ее себе так:
function Функциякорректировки(шаблон, диапазон) {
считываем формулу из шаблона;
пробегаем циклом по всем строкам диапазона {
если лист из правой ячейки не существует — выдаем ошибку;
если все ок, то ищем в формуле подстроку из левой ячейки и меняем на значение правой ячейки;
}
возвращаем новую формулу;
}
Ну и собственно вопрос/просьба: может быть вы сталкивались с подобной задачей и нашли какой-то более элегантный способ ее решения — прошу поделитесь. Ну, а если никакого другого варианта нет, помогите написать функцию. У меня знания VB отсутствуют, функцию я написать-напишу, но это потребует больших временных затрат на изучение базы, а у Вас, если такая функция возможна, займет пару тройку минут. Поэтому и прошу у Вас помощи в реализации.
К теме прилагаю простенький файл, для тестов.
|
В формуле использовать Имя листа из ячейки |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
|
0 / 0 / 0 Регистрация: 09.03.2011 Сообщений: 6 |
|
|
1 |
|
Изменение листа в формуле09.03.2011, 13:18. Показов 20151. Ответов 11
Здравствуйте! Такой вопрос: как в формуле заставить изменяться лист,при том что номер ячейки остается тот же? Например, чтобы свести данные с 10 листов в одну таблицу на 11-м. Я ввожу формулу в таблицу на 11м листе. И протягиваю ее в столбик. И чтобы при этом в каждой следующей ячейке изменялся лист (=1!D2, =2!D2, =3!D2 и т.п. где 1,2,3 — название листов).Заранее спасибо за ответ!
0 |
|
22 / 22 / 7 Регистрация: 05.08.2010 Сообщений: 95 |
|||||||||||||||
|
09.03.2011, 14:21 |
2 |
||||||||||||||
|
я делаю так — добавляю вспомогательный столбец (с названиями листов):
1 |
|
0 / 0 / 0 Регистрация: 09.03.2011 Сообщений: 6 |
|
|
09.03.2011, 14:46 [ТС] |
3 |
|
Ура!Получилось! Спасибо!
0 |
|
956 / 596 / 11 Регистрация: 11.06.2010 Сообщений: 1,345 |
|
|
09.03.2011, 15:49 |
4 |
|
Без доп столбца: Код =ДВССЫЛ("Лист"&СТРОКА()&"!D2")
1 |
|
0 / 0 / 0 Регистрация: 09.03.2011 Сообщений: 6 |
|
|
09.03.2011, 16:35 [ТС] |
5 |
|
Без доп столбца:
Пробую. Если листы называются по умолчанию (Лист1, Лист2) получается. А если у них имена, к примеру, 12, янв, февр, и т.п. не выходит
0 |
((((|
956 / 596 / 11 Регистрация: 11.06.2010 Сообщений: 1,345 |
|
|
09.03.2011, 17:02 |
6 |
|
Пробую. Если листы называются по умолчанию (Лист1, Лист2) получается. А если у них имена, к примеру, 12, янв, февр, и т.п. не выходит Естественно.
0 |
|
0 / 0 / 0 Регистрация: 09.03.2011 Сообщений: 6 |
|
|
10.03.2011, 16:25 [ТС] |
7 |
|
Листы у меня называются 12, янв, ферв, март, апр, май, анализ. Анализ — лист, на котором все и сводится.
0 |
|
956 / 596 / 11 Регистрация: 11.06.2010 Сообщений: 1,345 |
|
|
10.03.2011, 16:41 |
8 |
|
Листы у меня называются 12, янв, ферв, март, апр, май, анализ Ну если лист февраля действительно называется так: «ферв» — то только вручную
0 |
|
0 / 0 / 0 Регистрация: 09.03.2011 Сообщений: 6 |
|
|
12.03.2011, 14:14 [ТС] |
9 |
|
Ну в общем-то так они называются
0 |
. А можно тогда для двух вариантов, пожалуйста? Когда листы называются 01,02,03,04,анализ, и когда они называются январь, февраль, март, апрель, анализ?|
22 / 22 / 7 Регистрация: 05.08.2010 Сообщений: 95 |
|
|
12.03.2011, 14:43 |
10 |
|
можно конечно и поизвращаццо с формулой без доп. столбца — но у меня что-то желания нет( чем не устраивает первый вариант? с ним можно как угодно листы обзывать зы.если формулу необходимо и по столбцам растягивать, то можно сделать так Код =ДВССЫЛ(СЦЕПИТЬ($A2;"!D2")) но тогдапо всем столбцам ссылка на D2 будет… можно пример xls-файла выложить — быстрее вопрос решиццо
0 |
|
956 / 596 / 11 Регистрация: 11.06.2010 Сообщений: 1,345 |
|
|
12.03.2011, 22:43 |
11 |
|
…можно для двух вариантов? Когда листы называются 01,02,03,04,анализ, и когда они называются январь, февраль, март, апрель, анализ? Первый вариант: Код =ДВССЫЛ(ТЕКСТ(СТРОКА();"0#")&"!D2") второй вариант: Код =ДВССЫЛ(ТЕКСТ(ДАТА(1900;СТРОКА();1);"ММММ")&"!D2")
1 |
|
0 / 0 / 0 Регистрация: 09.03.2011 Сообщений: 6 |
|
|
15.03.2011, 23:27 [ТС] |
12 |
|
Большое спасибо!
0 |
|
IT_Exp Эксперт 87844 / 49110 / 22898 Регистрация: 17.06.2006 Сообщений: 92,604 |
15.03.2011, 23:27 |
|
12 |
Определяем имя листа в MS EXCEL
Смотрите также формулировка все же
пример, как я получается список именКопировать на А10:В12 тут не было
-61712-
То-есть надо преобразовать помогут (тут хорошиеkimна по данным о с той целью, д. – дочернимиОбе функции полезны дляПредполагая, что название книги
(Формулы/ Вычисления/ Пересчет).Определим имя листа с первична. Как сформулируете делаю иногда вручную. листовdavaispoem таких моих вопросов,vikttur
название из текстовой специалисты).: Можно такДВССЫЛ(D$2&»!$D$6:$D$17″) листах в определенном чтобы функция ПСТР объектами) функция ЛИСТ использования в документах, не содержит квадратныхПРИМЕНЕНИЕ: помощью функции ЯЧЕЙКА(). вопрос — такое
QQQennedy
ТЕКУЩЕЙ: Да, Михаил, ваша то не было: =ДВССЫЛ(E$9&»!»&ячейка) ячейки в имяВ противном случаеGuestМожно даже дедолларизацию диапазоне их расположения не учитывала символ недоступна, поскольку она содержащих большое количество скобок [ ],При изменении имениИмя листа можно определить
решение и получите.: Может есть какиекниги. Если вторым формула — именно бы столько эмоций
ячейка — ссылка
листа Excel? можно просто в: именно так, всем провести :) в рабочей книге «]». содержит схожую функцию. листов.
запишем формулу для
листа, все ссылки с помощью функции Давайте из этого альтернативные способы получить аргументом ПОЛУЧИТЬ.ЯЧЕЙКУ ничего то что я у наших замечательных, на ячеку.Артем нужные книги просто спасибо!ДВССЫЛ(D$2&»!D6:D17″) Excel.31 – максимальное количествоФункция листы имеет следующийЛист в Excel – изъятия из полученного
excel2.ru
Определение имени листа в MS EXCEL для использования в функции ДВССЫЛ()
в формулах автоматически ЯЧЕЙКА(), записав формулу исходить, а не формулой имя листа не ставить, то хотела, вы там умных и продвинутыхdavaispoem: Используйте формулу ДВССЫЛ() копировать модуль сxxxxcc xxxxccGuestGuest символов в названии
синтаксис: =ЛИСТЫ(ссылка). это таблица из результата имени листа: обновятся и будут (см. файл примера):
из того, что в ячейку? будет список имен маненько ячейки перепутали, мужчин…: Уважаемые мужчины, специальноslan функцией.: Подскажите как подставить: Спасибо, то что: Необходимо упростить процедуру листа.ссылка – объект ссылочного
- всех ячеек, отображаемых =ПСТР(B1;ПОИСК(«]»;B1)+1;ДЛСТР(B1)-ПОИСК(«]»;B1)-5); продолжать работать. Исключение=ПРАВСИМВ(ЯЧЕЙКА(«имяфайла»);ДЛСТР(ЯЧЕЙКА(«имяфайла»))-ПОИСК(«]»;ЯЧЕЙКА(«имяфайла»))) здесь должны додумыватьThe_Prist листов но я разобралась,А меня зовут для моей дамскоантиэкселевской: посмотрите в файлеИли как писал в формулу название нужно!
- ввода формулы таким=ЛИСТ() – данная функция типа, для которого на экране иЕсли в качестве названия составляет функция ДВССЫЛ(),Если формула вводится в
- что Вы имели: Можно вообще безАКТИВНОЙ по тропинке иду Ирина…тут вот…в низу логики, помогите пожалуйстаkodeks ранее Nic70y Думаю, текущего листаП.С. Я с
- образом, чтобы ссылку без параметра вернет требуется определить количество находящихся за его листа использовано число, в которой имя новую книгу, то ввиду, когда сказали
- макросов и всякихкниги. в гору…Для чего написано… применить формулу =ДВССЫЛ(E$9&»!»&ячейка),
: СПАСИБО что на будущееЮрий М функцией ДВССЫЛ и на имя листа номер текущего листа.
листов. Данный аргумент
пределами (всего 1 то функция ЯЧЕЙКА() листа может фигурировать ее сначала нужно что-то другое. макрофункций, которые какНу а дальше я все это
ЛМВ
покажите в файликеdavaispoem лучше разобраться …: Текущий = активный? пробовал, не получалось. в формуле можно В результате ее
excel2.ru
Примеры использования функций ЛИСТ и ЛИСТЫ в формулах Excel
является необязательным. Если 048 576 строк возвращает название книги в текстовой форме сохранить. После сохранения,По вопросу же
Функции ЛИСТ и ЛИСТЫ в Excel: описание аргументов и синтаксиса
и функции пользователя из имени «Листы» затеяла? У меня: Ирина. Оч. приятно. как это сделать.
: Как в вернуть
- пригодится Тогда ничего подставлять—- было брать из
- вычисления получим количество данный параметр не и 16 384 и листа в ДВССЫЛ(«Лист1!A1»). В статье возможно, потребуется нажать — вряд ли имеют проблемы с выковыриваем в ячейки 6 сводов (6 Я — Михаил.Ну не получается значение из листа,Вот как этот не надо..
- А как еще содержимого конкретной ячейки. листов в текущей
указан, функция вернет столбца). При отправке апострофах (‘), например, Определение имени листа клавишу
- получится без макросов пересчетом: эти самые имены участков), на участкахМне — конечно у меня… помогите пожалуйста. модуль копируется -xxxxcc xxxxcc сделать, чтобы при Т.е. в листе книге.
количество листов, содержащихся
- листа на печать ‘[_Определяем_имя_листа.xlsx]123456’!$A$1, что может для использования вF9 и макроформул, т.к.QQQennedy по порядку формулой
- разные адреса, адреса понравилось. Если быЛМВvikttur 1 секунда и: Нужно добавлении листов, их
- «сводные», во 2-йПример 2. В книге в книге, на он может быть привести к ошибке
- функции ДВССЫЛ() показано(Вкладка Формулы, Группа формулы не смогут: Не понятен момент,Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(«Название листа»&СТРОКА(C1)+СЛЧИС()*0&» - же и являются мне не нравилось: Уважаемая davaispoem, женская: Создаем себе проблемы? всё работает
дальше буду прописывать имена листов автоматически
- строчке находятся названия Excel содержится несколько одном из листов разбит на несколько при определении имени как использовать функцию Вычисление, Пересчет). определить кол-во листов как получить список «&ИНДЕКС(Листы;СТРОКА(C1));»») названием листов. Данные помогать по екселю
логика — она
- Пара основных правилxxxxcc xxxxcc ВПР , подставлять вставлялись во 2-ю листов, в которых листов. Необходимо:
- которой она была страниц. Поэтому нельзя листа; ЯЧЕЙКА(), чтобы сохранитьФормула вернет имя листа,
- в книге. листов данной формулой,Richman в листах идентичные
или учиться (ему
Как получить имя листа формулой в Excel
не хуже и обработки данных: правильная: спасибо буду пробывать значения строчку? Тут макрос введены однотипные данные.Вернуть номер текущего листа. записана. путать термины «лист»Записав в ячейке работоспособность формулы с содержащего ячейку сМихаил С. не могли бы: Выводит название текущего
по составу и же) — я
- не лучше мужской. структура и одинаковоеxxxxcc xxxxccЮрий М нужен? Листы постоянно добавляются/удаляются.Вернуть номер листа сПримечания: и «страница».В2
- функцией ДВССЫЛ(). вышеуказанной формулой. Если: Раз уж макросы в файле показать? листа в любую по расположению. Я бы не пользовался
- Она просто ДРУГАЯ. положение данных. Таблицы: походил вокруг да
: Если формула работаетDiana Листов очень много. названием «Статические_расчеты».Данная функция подсчитывает количествоКоличество листов в книгеформулу =ЕСЛИОШИБКА(ПОИСК(«‘»;B1);0), получим,
Примеры использования функции ЛИСТ и ЛИСТЫ
При изменении имени листа, требуется определить имя разрешены — не
- Понятно, что таким
- ячейку, куда вставили хотела сделать свод
- этим форумом. Я, опираясь на в листах нельзя около , и
с активным листом,
: а я Вам Хотелось бы, чтобы
- Вернуть номер листа «Динамические_расчеты», всех скрытых, очень
- ограничено лишь объемом что если название
- все ссылки в другого листа, то
проще ли выводить образом можно получить формулу с помощью ДВССЫЛ,Вы не написали, свою мужскую логику,
расположить одинаково?
Обработка информации о листах книги по формуле Excel
остановился на Вашем то не нужно. чтоли корреляцию массива формула в ячейке если его ячейка скрытых и видимых ОЗУ ПК. листа – число,
формулах автоматически обновятся
можно использовать туже список макросом, а
имя листа, в200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПРАВСИМВ(ЯЧЕЙКА(«имяфайла»;A1);ДЛСТР(ЯЧЕЙКА(«имяфайла»;A1))-НАЙТИ(«]»;ЯЧЕЙКА(«имяфайла»;A1);1)) чтобы применить его Вас мой пример
- считаю, что выбранныйЕсли только несколько варианте, правда размер Если ссылается на предложила? :)
- D3 и т.д. A3 содержит значение листов, за исключением
Функция ЛИСТ имеет в
то результат =1, и будут продолжать функцию ЯЧЕЙКА(), но не макрофункцией? котором находится ячейкаРаботает без макросов
exceltable.com
Имя листа в формуле
к другим сводам, — устроил или Вами подход - листов, стоит ли самой формулы смущает) другой лист, тоЭто ж пример содержала ссылку на 0. диаграмм, макросов и своем синтаксисе всего если текст, то работать. Исключение составляет с аргументом «адрес».The_Prist (в данном случаеAlexM просто меняя в нет? неверный. Во всяком заморачиваться с летучейNic70y нужно: был, а не лист, имя которогоВнесем данные в таблицу: диалогов. 1 аргумент и
0; функция ДВССЫЛ(), в
Для этого нужно, чтобы: Как вариант, чтобы А2 на текущем
: Еще так можно строке(8) свода наименованиеФайл я не
случае, с точки ДВССЫЛ()?: =ПСТР(ЯЧЕЙКА(«имяфайла»;A1);ПОИСК(«]»;ЯЧЕЙКА(«имяфайла»;A1))+1;99))
=Лист2!A1 решение…
указано в ячейкеДалее составим формулы дляЕсли в качестве параметра то не обязательныйСлегка модифицируем формулу в которой имя листа второй аргумент содержал формула пересчитывалась по листе, т.е. получаем200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПСТР(ЯЧЕЙКА(«имяфайла»;A1);НАЙТИ(«]»;ЯЧЕЙКА(«имяфайла»;A1);1)+1;99) адресов уже другого смог выложить, поскольку зрения Excel-я. НоdavaispoemМожно немного сократить,
WertyGuest
D2. Таким образом,
всех 4-х условий:
была передана недействительная
для заполнения: =ЛИСТ(значение).
ячейке может фигурировать в
ссылку на ячейку
Shift+F9 измените свою имя текущего листа)Хочу заметить, что
свода, которые совпадали не разрешено политикой если Вы хотите
: Одинаковое положение данных
где 99 максимальное: Вот функция -: Сорри, не разглядел добавив новый столбецдля условия №1 используем ссылка, результатом вычисленийзначение – необязательный аргумент
B3 текстовой форме ДВССЫЛ(«Лист1!A1»). другого листа, имя именованную формулу на
=ПСТР(ЯЧЕЙКА(«filename»;A2);ПОИСК(«]»;ЯЧЕЙКА(«filename»;A2))+1;31)Можно вместо А2 формула будет работать, бы с названиями
предприятия. делать так, как не получится, специально количество букв в
пользовательская. Может поможет столбцы G и с именем нового следующую формулу: =ЛИСТ()
является код ошибки
функции, который содержитдля определения названия В статье показано которого и будет такую: поставить ссылку на если книга была
в листах. НуПоэтому и пришлось мне кажется делать разбросала таблички в названии Листа.
xxxxcc xxxxcc J ;) листа, перетянув формулу
для условия №2 введем #ССЫЛКА!.
текстовые данные с листа: =ПСТР(B1;ПОИСК(«]»;B1)+1;ДЛСТР(B1)-ПОИСК(«]»;B1)-5-B2); как использовать функцию определено. Если формула=ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1)&ТЕКСТ(ТДАТА();»») ячейку другого листа сохранена. вот, нагородила… так долго и неправильно, то вот разные ячейки. Свод
vikttur: Спасибо , тему
Dimius_ с ячейки D2,
формулу: =ЛИСТ(«Статические_расчеты»)Данная функция недоступна в названием листа либо
planetaexcel.ru
Подстановка названия листа в формулу
Теперь, записав вместо формулы ЯЧЕЙКА(), чтобы сохранить =ЯЧЕЙКА(«адрес»;лист2!A1) находится наQQQennedy
(например лист1) =ПСТР(ЯЧЕЙКА(«filename»;’Лист1′!A2);ПОИСК(«]»;ЯЧЕЙКА(«filename»;’Лист1′!A2))+1;31)ИQQQennedyВот в моем нудно писать.
Вам формулы. большой, 50 листов
: xxxxcc xxxxcc, по можно закрывать: Вот сделал в
этот процесс былдля условия №3 запишем объектной модели в ссылку, для которой =ДВССЫЛ(«лист2!A1″) формулу =ДВССЫЛ(B3&»!A1″), работоспособность формулы с листе1 в ячейке
: Вот это и
опять возникает вопрос: Добрый день, уважаемые примере, так вам
Пришлете свой емайлДля листа Свод: с названием объектов,
Вам скучает п.4.2xxxxcc xxxxcc файле примера, может существенно упрощен.
формулу: связи с наличием
требуется установить номер мы решим задачу: функцией ДВССЫЛ().B1 требовалось узнать, буду с автоматическим получением знатоки!
не понятном, все (на мой емайл,в яч. E11: свод в длину, Правил.
: Werty, Ваша пользовательская кому пригодится…
GuestФункция ЕСЛИ выполняет проверку
там схожей функции. листа. Если данный изменение имени Листа2
Имя листа можно определить
, то имя листа в макрос запихивать. имен листов
Такая проблема, в в принципе и он — внизу, =ДВССЫЛ(E$9&»!»&»A»&СТРОКА(A2)) — и как я показаланужно бы почитать,
функция в других—-: Вот, собственно файл условия равенства значения,
параметр не указан, не повлияет на с помощью функции (Лист2) можно определить Спасибо
The_Prist прикрепленном файле на свелось, с вашей если что) -
протянуть до яч. на примере. Заменой
дабы не давать книгах не работаетП.С. Аппетит приходитvikttur хранящегося в ячейкеПример 1. При выполнении
функция вернет номер работоспособность формулы.
ЯЧЕЙКА(), об этом по формуле:З.Ы. Все таки: что спросили -
листе Пример в помощью конечно…спасибо…вот что смогу отправить на E13
долго. дополнительной работы модераторам.Nic70y
во время еды.: Тема рядом с A3 листа Динамические_расчеты,
расчетной работы студент листа, в однойВнимание! читайте в статье=ПСТР(B1;ПОИСК(«]»;B1)+1;ДЛСТР(B1)-ПОИСК(«]»;B1)-5) вопрос, озвученный в то и получили. 1й строке протянута получилось… него свой пример
в яч. F11:А если всетакиЮрий М: =ЯЧЕЙКА(«имяфайла»;A1)
Т.к. сделать, чтобы Вашей нулю или пустому
использовал программу Excel, из ячеек которого
planetaexcel.ru
как в формуле получить ссылку на лист (название листа в некой ячейке)
Иногда, когда открыто Определяем имя листа.В этом случае имя 1м посте всегда Вопрос был про формула, выдергивающая названияVIDEO56
екселевский. =ДВССЫЛ(E$9&»!»&»B»&СТРОКА(B2)) — и сделать таблички на: Да и пункт
Werty при добавлении листов,
Guest значению.
в которой создал она была записана.
несколько книг, функцияПредположим, что в ячейке листа не должно первичный, остальные уточняющие.
имя листа, а листов (используется именованный
: Добрый день, подскажитеПредоженный Вами пример протянуть до яч. одном месте, как 3.2 тоже…: Её нужно добавить
их имена листов: Или я вВ результате получим: книгу из несколько
Примечания: ЯЧЕЙКА() может работатьB4 состоять только из Не зря же не про список. диапазон «страницы»). Если формулу позволяющую переименовывать — из него F13 свести?
xxxxcc xxxxcc в Excel. Каким автоматически вставлялись во танке, или..
Пример 3. В книге листов. Для собственногоПри работе функции ЛИСТ некорректно. Для восстановленияна Листе1 имеется
цифр, т.к. если просите создавать отдельные Я и показал. попробовать добавить новый ячейку по названию мало что понятно.Формулы будут работать,davaispoem: я этот ник образом — пока 2-ю строчку?Там имя файла, Excel содержится несколько удобства, студент решил учитываются все листы, работоспособности формулы нужно формула =ДВССЫЛ(«лист2!A1»). Если в качестве названия темы по несвязаннымQQQennedy лист, значения в
листа!А просто по
если:: А если всетаки
создал лет с сам не знаю.Файл удален а мне имя листов. Необходимо определить в ячейках A2 которые являются видимыми,
нажать клавишу Лист2 переименовать в
листа использовано число, с 1м постом: Данный вопрос задан строке 1 наKSV опыту — часто1. названия листов сделать таблички на 8 назад, как Пока сам ищу- велик размер листа надо… общее количество листов, и B2 каждого скрытыми и оченьF9 Лист3, то вышеуказанная то функция ЯЧЕЙКА()
вопросам. Заканчиваем полемику,
с целью решить листе пример не: Добрый день! решение может быть
и шапки таблицы одном месте, как на меня он ответ
— [Diana
а также число листа выводить данные скрытыми. Исключениями являются(Формулы/ Вычисления/ Пересчет).
формула работать не возвращает название книги ещё раз спасибо
эту проблему, т.к. обновляются. Как справитьсяUDF
проще, чем казалось на листе Свод свести? ни кого неxxxxcc xxxxccМОДЕРАТОРЫ: так? листов, содержащихся между
о названии листа диалоги, макросы иПРИМЕЧАНИЕ: будет. Чтобы работоспособность и листа в XD как видно в с этой проблемой?VIDEO56 в начале (я — совпадают.Z
обижает, и на: мне нужно имя]
Guest листами «Статические_расчеты» и и его порядковом
диаграммы.С помощью обычных формулы сохранилась - апострофах (‘) иvikttur файле, получать имяQQQennedy: KSV, Спасибо. Норм
про себя).2. в листах: М-да: «… свод многих форумах люди
листа, или «имяDiana: нет. Нужно, чтобы «Экономические_расчеты».
номере соответственно. ДляЕсли аргументом функции является формул (не VBA)
определим имя листа формула вернет неправильный: Первичный. Естественно. Но листа в ячейке: наверное нужно добавить, работает!ps
данные занесены в в длину, как на него адекватно
файла» даст имя: 136 КБ! :) протягивая формулу изИсходная таблица имеет вид: этого он использовал текстовое значение, которое
невозможно определить имя
с помощью функции результат. Справиться с заходящие в тему я уже могу добавление/удаление листов происходит_Boroda_На этом сайте диапазон ячеек А1:В4. я показала…» - реагируют
листа? уволят :) ячейки D3, неОбщее количество листов вычислим следующие формулы:
не соответствует названию
активного листа
ЯЧЕЙКА() (см. файл
этим поможет формула часто (нехватка времени,The_Prist в макросе, но: Без макросов. — действительно многоПока окончательный смысл еще один изО цитировании: понялNic70yDiana было необходимости потом по формуле:Описание аргументов для функции ни одного изи адрес примера). =ПОДСТАВИТЬ(C1;»‘»;»»), которая удалит не очень интересно): ну это да. типичные методы пересчета1. Формулой, но замечательных, умных и Вашей идеи мне, примеров, как бы свою ошибку ,: =ПСТР(ЯЧЕЙКА(«имяфайла»;A1);ПОИСК(«]»;ЯЧЕЙКА(«имяфайла»;A1))+1;ДЛСТР(ЯЧЕЙКА(«имяфайла»;A1))): так не пойдет? переименовывать имя листаДля определения количества листов,
ПСТР: листов, содержащихся вактивной ячейкиЗапишем на Листе1 в символ апострофа. просматривают сообщения «по Ведь прям на
planetaexcel.ru
Присвоение ячейке имени листа (Формулы/Formulas)
формул в VBA там должна быть продвинутых. Мужчин и например, непонятен. сказать помягшее, -
постараюсь свести кТак будет имя
Guest
(Лист1 на Лист2 содержащихся между двумяЯЧЕЙКА(«имяфайла») – функция, возвращающая
книге, будет возвращена.
ячейкеТакже предполагается, что имя дигонали», видят вопрос языке крутится список (типа прописать Application.Calculate) ссылка на ячейку
женщин. Мужчин -Но когда Вы своебразной дамскоантиэкселевской логики… минимуму цитирование.
листа.: не-а, хотя за и т.д.), а
указанными листами, запишем
текст, в котором ошибка #НД.Функция ЛИСТ в ExcelB1 файла не содержит — дают ответ. листов книги, когда не помогают того листа, имя больше. Причину мы по выбранной Вами,
И как потомЮрий МWerty помощь спасибо. Нужно чтоб имя листа
формулу: функция ПСТР выполняет
Если в качестве аргумента возвращает числовое значение,формулу =ЯЧЕЙКА(«адрес»;лист2!A1) Формула символа закрывающая квадратнаяТут Вам замечание:
просят имя листа
Vik_tor
которого выводим - (все) уже обсудили. не лучшей, на общие бабки подбивать,: Перечитайте Правила и: Вот с форума получить диапазон, точнее
excelworld.ru
Получение списка имен листов формулой
бралось автоматически изСтатические_расчеты:Экономические_расчеты!A2 – ссылка на поиск заданного количества
функции было передано соответствующее номеру листа, вернет результат [_Определяем_имя_листа.xlsx]Лист2!$A$1, скобка ( ] название темы («ПересчетМогу с таким: В приложенном файле не интересно. Никуда не деться, мой взгляд, тропинке выборку по именам/продуктам сделайте вывод. нашёл:
строку с именами ячейки во 2-й ячейку A2 диапазона символов. В данном недействительное значение, результатом на который указывает т.е. полный адрес ).
формулы») не отражало же успехом сказать, нет именованного диапазона
2. Хоть макросов статистика — вещь подниметесь на вершину,
делать?!. Не первыйh1dexИсточник: hardforum.ru/t56638/ всех листов. Нашел строке (с именами листов между «Статические_расчеты» случае вернется значение
ее вычислений будет ссылка, переданная функции ячейки с указаниемВНИМАНИЕ! задачи. И если
что Вы придираетесьQQQennedy в файле и упрямая :) то однажды оттуда раз подобный вопрос,: ..
xxxxcc xxxxcc в Plex подобный листов). Сделал скриншот, и «Экономические_расчеты» включая «C:UserssoulpDesktop[ЛИСТ_ЛИСТЫ.xlsx]Статические расчеты», где являться ошибка #ССЫЛКА!.
в качестве параметра. названия книги иИногда, когда открыто бы Вы четко к решению -: мой косяк, перезалил нет, но ониvikttur увидите более легкие и не первыйkodeks: мне кажется я макрос, создающий оглавление. может так понятнее эти листы.
после символа «]»В рамках объектной моделиФункция ЛИСТЫ в Excel имени листа (ссылка несколько книг, функция обозначили проблему в оно так же
файл должны быть разрешены.: Учитесь. Политика предприятия, пути :) раз советую маленькую: Как в формуле такое не осилю( Попробую разобраться, как
будет…Для получения искомого значения находится искомый текст (иерархия объектов на возвращает числовое значение, должна быть на
ЯЧЕЙКА() может работать названии темы, возможно, даст список листов,Vik_torИменами (Контрл F3). понимаешь ли… Нормальныйdavaispoem универсальную бесплатную приблуду
получить ссылку наWerty его переделать дляКазанский было вычтено число – название листа. VBA, в которой которое соответствует количеству столбец с названием некорректно. Для восстановления и помощь пришла если создать формулу: Попробуйте макросом же
Там имя «Листы» способ наладить контакты: Большое вам спасибо! для сбора данных ячейку находящуюся на: Тогда сделайте отдельную моей задачи
: Ну да, ДВССЫЛ. 2.НАЙТИ(«]»;ЯЧЕЙКА(«имяфайла»))+1 – функция, возвращающая Application является главным листов, на которые из одной буквы,
работоспособности формулы нужно бы быстрее и на каждый лист произвести замену = с формулой :)
Я надеюсь, вам
— как вариант другом листе, а тему по этомуDianaЗамените ссылки типа
В результате получим следующее: номер позиции символа объектом, а Workbook, предоставлена ссылка. например, ссылка лист2!AВ1 нажать клавишу качественнее.Поэтому давайте без на = в200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПОДСТАВИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1;ПОЛУЧИТЬ.ЯЧЕЙКУ(66;Фрося!$A$1));»[«&ПОЛУЧИТЬ.ЯЧЕЙКУ(66;Фрося!$A$1)&»]»;)В А10 лучше:
все это самому (полуфабрикат) вложение со имя листа взать вопросу — точно: нда…Лист1!$D$6:$D$17Формула выводит подробную информацию «]», единица добавлена
Worksheer и т.Примечания: не годится);F9Тему переименовал. подобных высказываний - первой строке листаВ имени «Листы»=СУММЕСЛИ($C$9:$J$9;A$9;$C10:$J10) понравилось…ведь если бы
скрином внутри… ;)
planetaexcel.ru
из текстовой ячейки?
Skip to content
В этой статье объясняется синтаксис функции ДВССЫЛ, основные способы ее использования и приводится ряд примеров формул, демонстрирующих использование ДВССЫЛ в Excel.
В Microsoft Excel существует множество функций, некоторые из которых просты для понимания, другие требуют длительного обучения. При этом первые используются чаще, чем вторые. И тем не менее, функция Excel ДВССЫЛ (INDIRECT на английском) является единственной в своем роде. Эта функция Excel не выполняет никаких вычислений, не оценивает никаких условий не ищет значения.
Итак, что такое функция ДВССЫЛ (INDIRECT) в Excel и для чего ее можно использовать? Это очень хороший вопрос, и, надеюсь, вы получите исчерпывающий ответ через несколько минут, когда закончите чтение.
Функция ДВССЫЛ в Excel — синтаксис и основные способы использования
ДВССЫЛ используется для создания косвенных ссылок на ячейки, диапазоны, другие листы или книги. Другими словами, она получает нужный адрес и позволяет вам при помощи формулы создать из него динамическую ссылку на ячейку или диапазон вместо их прямого указания. В результате вы можете изменить ссылку в формуле, не изменяя саму формулу. Более того, эти косвенные ссылки не изменятся при вставке на лист новых строк или столбцов или при удалении уже существующих.
Все это может быть проще понять на примере. Однако чтобы написать формулу, пусть даже самую простую, нужно знать аргументы функции, верно? Итак, давайте сначала кратко рассмотрим синтаксис Excel ДВССЫЛ.
Функция ДВССЫЛ в Excel возвращает ссылку на ячейку, используя текстовую строку. Она имеет два аргумента, первый является обязательным, а второй – нет:
ДВССЫЛ(ссылка_на_ячейку; [a1])
ссылка_на_ячейку – это адрес нужной ячейки в виде текстовой строки, либо названия именованного диапазона.
a1 — логическое значение, указывающее, какой тип ссылки содержится в первом аргументе:
- Если значение ИСТИНА или опущено, то используется ссылка на ячейку в стиле A1.
- Если ЛОЖЬ, то возвращается ссылка в виде R1C1.
Таким образом, ДВССЫЛ возвращает либо ссылку на ячейку, либо ссылку на диапазон.
Хотя тип ссылки R1C1 может быть полезен в определенных ситуациях, вам, вероятно, удобнее использовать привычные ссылки типа A1. В любом случае, почти все формулы в этом руководстве будут использовать ссылки A1, и поэтому мы будем просто опускать второй аргумент в функции.
Как работает функция ДВССЫЛ
Чтобы получить представление о работе функции, давайте создадим простую формулу, которая демонстрирует, как можно применить ДВССЫЛ в Excel.
Предположим, у вас есть число 5 в ячейке A1 и текст «A1» в ячейке C1. Теперь поместите формулу =ДВССЫЛ(C1) в любую другую ячейку и посмотрите, что произойдет:
- Функция ДВССЫЛ обращается к значению в ячейке C1. Там в виде текстовой строки записан адрес «A1».
- Функция ДВССЫЛ направляется по этому адресу в ячейку A1, откуда извлекает записанное в ней значение, то есть число 555.
Итак, в этом примере функция ДВССЫЛ преобразует текстовую строку в ссылку на ячейку.
Аналогичным образом можно получить ссылку на диапазон. Для этого просто нужно функции ДВССЫЛ указать два адреса – начальный и конечный. Вы видите это на скриншоте ниже.
Формула ДВССЫЛ(C1&»:»&C2) извлекает адреса из указанных ячеек и превращается в =ДВССЫЛ(«A1:A5»).
В итоге мы получаем ссылку =A1:A5
Если вы считаете, что это все еще имеет очень мало практического смысла, пожалуйста, читайте дальше, и я продемонстрирую вам еще несколько примеров, которые раскрывают реальную силу функции Excel ДВССЫЛ и более подробно показывают, как она работает.
Как использовать ДВССЫЛ в Excel — примеры формул
Как показано в приведенном выше примере, вы можете использовать функцию ДВССЫЛ, чтобы записать адрес ячейки как обычную текстовую строку и получить в результате значение этой ячейки. Однако этот простой пример — не более чем намек на возможности ДВССЫЛ.
При работе с реальными данными мы можем превратить любой текст в ссылку, включая очень сложные комбинации, которые вы создаете, используя значения других ячеек и результаты, возвращаемые другими формулами Excel. Но не будем торопиться и пойдем от простого к сложному.
Создание косвенных ссылок из значений ячеек
Как вы помните, функция ДВССЫЛ в Excel позволяет использовать стили ссылок A1 и R1C1. Обычно вы не можете использовать оба стиля на одном листе одновременно. Вы можете переключаться между двумя типами ссылок только с помощью опции «Файл» > «Параметры» > «Формулы» > R1C1 . По этой причине пользователи Excel редко рассматривают использование R1C1 в качестве альтернативного подхода к созданию ссылок.
В формуле ДВССЫЛ вы можете использовать любой тип ссылки на одном и том же листе, если хотите. Прежде чем мы двинемся дальше, давайте более подробно рассмотрим разницу между стилями ссылок A1 и R1C1.
Стиль A1 — это обычный и привычный всем нам тип адресации в Excel, который указывает сначала столбец, за которым следует номер строки. Например, B2 обозначает ячейку на пересечении столбца B и строки 2.
Стиль R1C1 является обозначает координаты ячейки наоборот – за строками следуют столбцы, и к этому нужно привыкнуть:) Например, R5C1 относится к ячейке A5, которая находится в строке 5, столбце 1 на листе. Если после буквы не следует какая-либо цифра, значит, вы имеете в виду ту же строку или столбец, в которых записана сама формула.
А теперь давайте сравним на простом примере, как функция ДВССЫЛ обрабатывает адреса вида A1 и R1C1:
Как вы видите на скриншоте выше, две разные формулы возвращают один и тот же результат. Вы уже поняли, почему?
- Формула в ячейке D1: =ДВССЫЛ(C1)
Это самый простой вариант. Формула обращается к ячейке C1, извлекает ее значение — текстовую строку «A2» , преобразует ее в ссылку на ячейку, переходит к ячейке A2 и возвращает ее значение, равное 456.
- Формула в ячейке D3: =ДВССЫЛ(C3;ЛОЖЬ)
ЛОЖЬ во втором аргументе указывает, что указанное значение (C3) следует рассматривать как ссылку на ячейку в формате R1C1, т. е. сначала идет номер строки, за которым следует номер столбца. Таким образом, наша формула ДВССЫЛ интерпретирует значение в ячейке C3 (R2C1) как ссылку на ячейку на пересечении строки 2 и столбца 1, которая как раз и является ячейкой A2.
Создание ссылок из значений ячеек и текста
Аналогично тому, как мы создавали ссылки из значений ячеек , вы можете комбинировать текстовую строку и ссылку на ячейку с адресом прямо в формуле ДВССЫЛ, соединив их вместе при помощи оператора конкатенации (&).
В следующем примере формула =ДВССЫЛ(«А»&C1) возвращает значение из ячейки А1 на основе следующей логической цепочки:
Функция ДВССЫЛ объединяет элементы в первом аргументе ссылка_на_ячейку — текст «А» и значение из ячейки C1. Значение в C1 – это число 1, что в результате формирует адрес А1. Формула переходит к ячейке А1 и возвращает ее значение – 555.
Использование функции ДВССЫЛ с именованными диапазонами
Помимо создания ссылок на ячейки из текстовых строк, вы можете заставить функцию Excel ДВССЫЛ создавать ссылки на именованные диапазоны.
Предположим, у вас есть следующие именованные диапазоны на вашем листе:
- Яблоки – С2:E2
- Лимоны — C3: E3
- Апельсины – C4:E4 и так далее по каждому товару.
Чтобы создать динамическую ссылку Excel на любой из указанных выше диапазонов с цифрами продаж, просто запишите его имя, скажем, в H1, и обратитесь к этой ячейке при помощи формулы =ДВССЫЛ(H1).
А теперь вы можете сделать еще один шаг и вложить эту формулу в другие функции Excel. Например, попробуем вычислить сумму и среднее значений в заданном именованном диапазоне или найти максимальную и минимальную сумму продаж в нём, как это сделано на скриншоте ниже:
- =СУММ(ДВССЫЛ (H1))
- =СРЗНАЧ(ДВССЫЛ (H1))
- =МАКС(ДВССЫЛ (H1))
- =МИН(ДВССЫЛ (H1))
Теперь, когда вы получили общее представление о том, как работает функция ДВССЫЛ в Excel, мы можем поэкспериментировать с более серьёзными формулами.
ДВССЫЛ для ссылки на другой рабочий лист
Полезность функции Excel ДВССЫЛ не ограничивается созданием «динамических» ссылок на ячейки. Вы также можете использовать ее для формирования ссылки на другие листы.
Предположим, у вас есть важные данные на листе 1, и вы хотите получить эти данные на листе 2. На скриншоте ниже показано, как можно справиться с этой задачей.
Нам поможет формула:
=ДВССЫЛ(«‘»&A2&»‘!»&B2&C2)
Давайте разбираться, как работает эта формула.
Как вы знаете, обычным способом сослаться на другой лист в Excel является указание имени этого листа, за которым следуют восклицательный знак и ссылка на ячейку или диапазон, например Лист1!A1:С10. Так как имя листа часто содержит пробелы, вам лучше заключить его (имя, а не пробел 
в одинарные кавычки, чтобы предотвратить возможную ошибку, например,
‘Лист 1!’$A$1 или для диапазона – ‘Лист 1!’$A$1:$С$10 .
Наша задача – сформировать нужный текст и передать его функции ДВССЫЛ. Все, что вам нужно сделать, это:
- записать имя листа в одну ячейку,
- букву столбца – в другую,
- номер строки – в третью,
- объединить всё это в одну текстовую строку,
- передать этот адрес функции ДВССЫЛ.
Помните, что в текстовой строке вы должны заключать каждый элемент, кроме номера строки, в двойные кавычки и затем связать все элементы в единое целое с помощью оператора объединения (&).
С учетом вышеизложенного получаем шаблон ДВССЫЛ для создания ссылки на другой лист:
ДВССЫЛ («‘» & имялиста & «‘!» & имя столбца нужной ячейки & номер строки нужной ячейки )
Возвращаясь к нашему примеру, вы помещаете имя листа в ячейку A2 и вводите адреса столбца и строки в B2 и С2, как показано на скриншоте выше. В результате вы получите следующую формулу:
ДВССЫЛ(«‘»&A2&»‘!»&B2&C2)
Кроме того, обратите внимание, что если вы копируете формулу в несколько ячеек, вам необходимо зафиксировать ссылку на имя листа, используя абсолютные ссылки на ячейки, например $A$2.
Замечание.
- Если какая-либо из ячеек, содержащих имя листа и адреса ячеек (A2, B2 и c2 в приведенной выше формуле), будет пуста, ваша формула вернет ошибку. Чтобы предотвратить это, вы можете обернуть функцию ДВССЫЛ в функцию ЕСЛИ :
ЕСЛИ(ИЛИ(A2=»»;B2=»»;C2-“”); «»; ДВССЫЛ(«‘»&A2&»‘!»&B2&C2)
- Чтобы формула ДВССЫЛ, ссылающаяся на другой лист, работала правильно, указанный лист должен быть открыт в Экселе, иначе формула вернет ошибку #ССЫЛКА. Чтобы не видеть сообщение об ошибке, которое может портить вид вашей таблицы, вы можете использовать функцию ЕСЛИОШИБКА, которая будет отображать пустую строку при любой возникшей ошибке:
ЕСЛИОШИБКА(ДВССЫЛ(«‘»&A2&»‘!»&B2&C2); «»)
Формула ДВССЫЛ для ссылки на другую книгу Excel
Формула, которая создает ссылку на другую книгу Excel, основана на том же подходе, что и обычная ссылка на другую электронную таблицу. Вам просто нужно указать имя книги дополнительно к имени листа и адресу ячейки.
Чтобы упростить задачу, давайте начнем с создания ссылки на другую книгу обычным способом (апострофы добавляются, если имена вашей книги и/или листа содержат пробелы):
‘[Имя_книги.xlsx]Имя_листа’!Арес_ячейки
Но, чтобы формула была универсальной, лучше апострофы добавлять всегда – лишними не будут .
Предполагая, что название книги находится в ячейке A2, имя листа — в B2, а адрес ячейки — в C2 и D2, мы получаем следующую формулу:
=ДВССЫЛ(«‘[«&$A$2&».xlsx]»&$B$2&»‘!»&C2&D2)
Поскольку вы не хотите, чтобы ячейки, содержащие имена книг и листов, изменялись при копировании формулы в другие ячейки, вы можете зафиксировать их, используя абсолютные ссылки на ячейки – $A$2 и $B$2 соответственно.
Если адреса ячеек заменить их значениями, то мы получим такой промежуточный результат:
=ДВССЫЛ(«‘[INDIRECT.xlsx]Продажи’!D3»)
Ну а итоговый результат вы видите на скриншоте ниже.
Hbc6
И теперь вы можете легко создать собственную динамическую ссылку на другую книгу Excel, используя следующий шаблон:
=ДВССЫЛ(«‘[» & Название книги & «]» & Имя листа & «‘!» & Адрес ячейки )
Примечание. Рабочая книга, на которую ссылается ваша формула, всегда должна быть открыта, иначе функция ДВССЫЛ выдаст ошибку #ССЫЛКА. Как обычно, функция ЕСЛИОШИБКА может помочь вам избежать этого:
=ЕСЛИОШИБКА(ДВССЫЛ(«‘[«&$A$2&».xlsx]»&$B$2&»‘!»&C2&D2); «»)
Использование функции Excel ДВССЫЛ чтобы зафиксировать ссылку на ячейку
Обычно Microsoft Excel автоматически изменяет ссылки на ячейки при вставке новых или удалении существующих строк или столбцов на листе. Чтобы этого не произошло, вы можете использовать функцию ДВССЫЛ для работы с конкретными адресами ячеек, которые в любом случае должны оставаться неизменными.
Чтобы проиллюстрировать разницу, сделайте следующее:
- Введите любое значение в любую ячейку, например, число 555 в ячейку A1.
- Обратитесь к A1 из двух других ячеек тремя различными способами: =A1, =ДВССЫЛ(«A1») и ДВССЫЛ(С1), где в С1 записан адрес «А1».
- Вставьте новую строку над строкой 1.
Видите, что происходит? Ячейка с логическим оператором =А1 по-прежнему возвращает 555, потому что ее формула была автоматически изменена на =A2 после вставки строки. Ячейки с формулой ДВССЫЛ теперь возвращают нули, потому что формулы в них не изменились при вставке новой строки и они по-прежнему ссылаются на ячейку A1, которая в настоящее время пуста:
После этой демонстрации у вас может сложиться впечатление, что функция ДВССЫЛ больше мешает, чем помогает. Ладно, попробуем по-другому.
Предположим, вы хотите просуммировать значения в ячейках A2:A5, и вы можете легко сделать это с помощью функции СУММ:
=СУММ(A2:A5)
Однако вы хотите, чтобы формула оставалась неизменной, независимо от того, сколько строк было удалено или вставлено. Самое очевидное решение — использование абсолютных ссылок — не поможет. Чтобы убедиться, введите формулу =СУММ($A$2:$A$5) в какую-нибудь ячейку, вставьте новую строку, скажем, в строку 3, и увидите формулу, преобразованную в =СУММ($A$2:$A$6).
Конечно, такая любезность Microsoft Excel в большинстве случаев будет работать нормально. Тем не менее, могут быть сценарии, когда вы не хотите, чтобы формула менялась автоматически. Например, нам нужна сумма только первых четырех значений из таблицы.
Решение состоит в использовании функции ДВССЫЛ, например:
=СУММ(ДВССЫЛ(«A2:A5»))
Поскольку Excel воспринимает «A1: A5» как простую текстовую строку, а не как ссылку на диапазон, он не будет вносить никаких изменений при вставке или удалении строки (строк), а также при их сортировке.
Использование ДВССЫЛ с другими функциями Excel
Помимо СУММ, ДВССЫЛ часто используется с другими функциями Excel, такими как СТРОКА, СТОЛБEЦ, АДРЕС, ВПР, СУММЕСЛИ и т. д.
Пример 1. Функции ДВССЫЛ и СТРОКА
Довольно часто функция СТРОКА используется в Excel для возврата массива значений. Например, вы можете использовать следующую формулу массива (помните, что для этого нужно нажать Ctrl + Shift + Enter), чтобы вернуть среднее значение трех наименьших чисел в диапазоне B2:B13
{=СРЗНАЧ(НАИМЕНЬШИЙ(B2:B13;СТРОКА(1:3)))}
Однако, если вы вставите новую строку в свой рабочий лист где-нибудь между строками 1 и 3, диапазон в функции СТРОКА изменится на СТРОКА(1:4), и формула вернет среднее значение четырёх наименьших чисел вместо трёх.
Чтобы этого не произошло, вставьте ДВССЫЛ в функцию СТРОКА, и ваша формула массива всегда будет оставаться правильной, независимо от того, сколько строк будет вставлено или удалено:
={СРЗНАЧ(НАИМЕНЬШИЙ(B2:B13;СТРОКА(ДВССЫЛ(«1:3»))))}
Аналогично, если нам нужно найти сумму трёх наибольших значений, можно использовать ДВССЫЛ вместе с функцией СУММПРОИЗВ.
Вот пример:
={СУММПРОИЗВ(НАИБОЛЬШИЙ(B2:B13;СТРОКА(ДВССЫЛ(«1:3»))))}
А чтобы указать переменное количество значений, которое мы хотим сосчитать, можно это число вынести в отдельную ячейку. К примеру, в С1 запишем цифру 3. Тогда формулу можно изменить таким образом:
={СУММПРОИЗВ(НАИБОЛЬШИЙ(B2:B13;СТРОКА(ДВССЫЛ(«1:»&C1))))}
Согласитесь, что получается достаточно гибкий расчёт.
Пример 2. Функции ДВССЫЛ и АДРЕС
Вы можете использовать Excel ДВССЫЛ вместе с функцией АДРЕС, чтобы получить значение в определенной ячейке на лету.
Как вы помните, функция АДРЕС используется в Excel для получения адреса ячейки по номерам строк и столбцов. Например, формула =АДРЕС(1;3) возвращает текстовую строку «$C$1», поскольку C1 — это ячейка на пересечении 1-й строки и 3-го столбца.
Чтобы создать ссылку на ячейку, вы просто встраиваете функцию АДРЕС в формулу ДВССЫЛ, например:
=ДВССЫЛ(АДРЕС(1;3))
Конечно, эта несложная формула лишь демонстрирует технику. Более сложные примеры использования функций ДВССЫЛ И АДРЕС в Excel см. в статье Как преобразовать строки в столбцы в Excel .
И вот еще несколько примеров формул в которых используется функция ДВССЫЛ, и которые могут оказаться полезными:
- ВПР и ДВССЫЛ — как динамически извлекать данные из разных таблиц (см. пример 2).
- Excel ДВССЫЛ и СЧЁТЕСЛИ — как использовать функцию СЧЁТЕСЛИ в несмежном диапазоне или нескольких выбранных ячейках.
Использование ДВССЫЛ для создания выпадающих списков
Вы можете использовать функцию Excel ДВССЫЛ с инструментом проверки данных для создания каскадных выпадающих списков. Они показывают различные варианты выбора в зависимости от того, какое значение пользователь указал в предыдущем выпадающем списке.
Простой зависимый раскрывающийся список сделать очень просто. Все, что требуется, — это несколько именованных диапазонов для хранения элементов раскрывающегося списка.
В ячейке А1 вы создаете простой выпадающий список с названиями имеющихся именованных диапазонов. Для второго зависимого выпадающего списка в ячейке В2 вы используете простую формулу =ДВССЫЛ(A1), где A1 — это ячейка, в которой выбрано имя нужного именованного диапазона.
К примеру, выбрав в первом списке второй квартал, во втором списке мы видим месяцы этого квартала.
Рис9
Чтобы сделать более сложные трехуровневые меню или раскрывающиеся списки с многоуровневыми записями, вам понадобится немного более сложная формула ДВССЫЛ в сочетании с вложенной функцией ПОДСТАВИТЬ.
Подробное пошаговое руководство по использованию ДВССЫЛ с проверкой данных Excel смотрите в этом руководстве: Как создать зависимый раскрывающийся список в Excel.
Функция ДВССЫЛ Excel — возможные ошибки и проблемы
Как показано в приведенных выше примерах, функция ДВССЫЛ весьма полезна при работе со ссылками на ячейки и диапазоны. Однако не все пользователи Excel охотно принимают этот подход, в основном потому, что постоянное использование ДВССЫЛ приводит к отсутствию прозрачности формул Excel и несколько затрудняет их понимание. Функцию ДВССЫЛ сложно просмотреть и проанализировать ее работу, поскольку ячейка, на которую она ссылается, не является конечным местоположением значения, используемого в формуле. Это действительно довольно запутанно, особенно при работе с большими сложными формулами.
В дополнение к сказанному выше, как и любая другая функция Excel, ДВССЫЛ может вызвать ошибку, если вы неправильно используете аргументы функции. Вот список наиболее типичных ошибок и проблем:
Ошибка #ССЫЛКА!
Чаще всего функция ДВССЫЛ возвращает ошибку #ССЫЛКА! в следующих случаях:
- Аргумент ссылка_на_ячейку не является допустимой ссылкой Excel. Если вы пытаетесь передать функции текст, который не может обозначать ссылку на ячейку (например, «A1B0»), то формула приведет к ошибке #ССЫЛКА!. Во избежание возможных проблем проверьте аргументы функции ДВССЫЛ .
- Превышен предел размера диапазона. Если аргумент ссылка_на_ячейку вашей формулы ДВССЫЛ ссылается на диапазон ячеек за пределами строки 1 048 576 или столбца 16 384, вы также получите ошибку #ССЫЛКА в Excel 2007 и новее. Более ранние версии Excel игнорируют превышение этого лимита и действительно возвращают некоторое значение, хотя часто не то, что вы ожидаете.
- Используемый в формуле лист или рабочая книга закрыты.Если ваша формула с ДВССЫЛ адресуется на другую книгу или лист Excel, то эта другая книга или электронная таблица должны быть открыты, иначе ДВССЫЛ возвращает ошибку #ССЫЛКА! . Впрочем, это требование характерно для всех формул, которые ссылаются на другие рабочие книги Excel.
Ошибка #ИМЯ?
Это самый очевидный случай, подразумевающий, что в названии функции есть какая-то ошибка.
Ошибка из-за несовпадения региональных настроек.
Также распространенная проблема заключается не в названии функции ДВССЫЛ, а в различных региональных настройках для разделителя списка.
В европейских странах запятая зарезервирована как десятичный символ, а в качестве разделителя списка используется точка с запятой.
В стандартной конфигурации Windows для Северной Америки и некоторых других стран разделителем списка по умолчанию является запятая.
В результате при копировании формулы между двумя разными языковыми стандартами Excel вы можете получить сообщение об ошибке « Мы обнаружили проблему с этой формулой… », поскольку разделитель списка, используемый в формуле, отличается от того, что установлен на вашем компьютере. Если вы столкнулись с этой ошибкой при копировании какой-либо НЕПРЯМОЙ формулы из этого руководства в Excel, просто замените все запятые (,) точками с запятой (;) (либо наоборот). В обычных формулах Excel эта проблема, естественно, не возникнет. Там Excel сам поменяет разделители исходя из ваших текущих региональных настроек.
Чтобы проверить, какие разделитель списка и десятичный знак установлены на вашем компьютере, откройте панель управления и перейдите в раздел «Регион и язык» > «Дополнительные настройки».
Надеемся, что это руководство пролило свет для вас на использование ДВССЫЛ в Excel. Теперь, когда вы знаете ее сильные стороны и ограничения, пришло время попробовать и посмотреть, как функция ДВССЫЛ может упростить ваши задачи в Excel. Спасибо за чтение!
Вот еще несколько статей по той же теме:
Как удалить сразу несколько гиперссылок — В этой короткой статье я покажу вам, как можно быстро удалить сразу все нежелательные гиперссылки с рабочего листа Excel и предотвратить их появление в будущем. Решение работает во всех версиях Excel,… 
Как использовать функцию ГИПЕРССЫЛКА — В статье объясняются основы функции ГИПЕРССЫЛКА в Excel и приводятся несколько советов и примеров формул для ее наиболее эффективного использования. Существует множество способов создать гиперссылку в Excel. Чтобы сделать ссылку на… Гиперссылка в Excel: как сделать, изменить, удалить — В статье разъясняется, как сделать гиперссылку в Excel, используя 3 разных метода. Вы узнаете, как вставлять, изменять и удалять гиперссылки на рабочих листах, а также исправлять неработающие ссылки. Гиперссылки широко используются… 
Как сделать зависимый выпадающий список в Excel? — Одной из наиболее полезных функций проверки данных является возможность создания выпадающего списка, который позволяет выбирать значение из предварительно определенного перечня. Но как только вы начнете применять это в своих таблицах,…
Разберем несколько вариантов добавления имени листа в ячейку в Excel с помощью формул.
Чтобы присвоить ячейке имя листа в Excel можно воспользоваться следующей формулой:
=ПСТР(ЯЧЕЙКА(«ИМЯФАЙЛА»;A1);ПОИСК(«]»;ЯЧЕЙКА(«ИМЯФАЙЛА»;A1))+1;255)
Давайте по шагам разберем принцип действия формулы имени листа.
Шаг 1. Функция ЯЧЕЙКА
Функция ЯЧЕЙКА позволяет получить данные о содержимом ссылки, в том числе и имя файла.
В данном случае формула ЯЧЕЙКА(«ИМЯФАЙЛА»;A1) позволяет получить полный путь файла Excel на локальном диске:
Как мы видим название листа идет сразу после названия файла, обрамленного в квадратные скобки.
Шаг 2. Функция ПОИСК
Таким образом для извлечения имени листа необходимо найти символ закрывающейся квадратной скобки (]) с помощью функции ПОИСК, которая возвращает позицию первого вхождения искомого элемента:
Прибавляя к результату 1, мы получаем позицию с которой начинается имя листа.
Шаг 3. Функция ПСТР
После нахождения квадратной скобки нам достаточно извлечь из полного названия файла правую часть, применив функцию ПСТР.
Данная функция возвращает заданное количество знаков, начиная с указанной позиции.
Так как точное количество символов в имени листа неизвестно, то в качестве последнего аргумента функции ПСТР указываем заведомо большее число, чем длина имени листа (подойдет любое число больше 31 — максимальная длина названия листа).
Альтернативная формула
Чтобы вставить название листа в ячейку можно воспользоваться альтернативной формулой:
=ПРАВСИМВ(ЯЧЕЙКА(«ИМЯФАЙЛА»;A1);ДЛСТР(ЯЧЕЙКА(«ИМЯФАЙЛА»;A1))-ПОИСК(«]»;ЯЧЕЙКА(«ИМЯФАЙЛА»;A1)))
Отличие от предыдущего варианта заключается в использовании функции ПРАВСИМВ, которая возвращает указанное количество знаков с конца текста.
Описание используемых функций
Функция ЯЧЕЙКА:
ЯЧЕЙКА(тип_сведений; [ссылка])
Возвращает сведения о форматировании, адресе или содержимом первой ячейки ссылки.
- Тип сведений (обязательный аргумент) — текстовое значение, задающее тип сведений о ячейке (например, адрес, столбец, цвет, имяфайла, формат, скобки и т.д.);
- Ссылка (необязательный аргумент) — ячейка, по которой возвращаются данные.
Функция ПОИСК:
ПОИСК(искомый_текст; просматриваемый_текст; [начальная_позиция])
Возвращает позицию первого вхождения знака или строки текста (при чтении слева направо, прописные и строчные буквы не различаются).
- Искомый текст (обязательный аргумент) — искомый текст
- Просматриваемый текст (обязательный аргумент) — текст, по которому производится поиск;
- Начальная позиция (необязательный аргумент) — номер знака в просматриваемом тексте, с которого следует начать поиск.
Функция ПСТР:
ПСТР(текст; начальная_позиция; число_знаков)
Возвращает заданное число знаков из строки текста, начиная с указанной позиции.
- Текст (обязательный аргумент) — текст, из которого извлекают символы;
- Начальная позиция (обязательный аргумент) — позиция первого знака, извлекаемого из текста;
- Число знаков (обязательный аргумент) — количество знаков, возвращаемых функцией.
Удачи вам и до скорой встречи на страницах блога Tutorexcel.ru!