Как сделать динамическую ссылку в excel

Мультивыбор

Часто нам необходимо получить несколько значений из набора данных. Можно вывести их в разные ячейки, а можно объединить в одну. В любом случае необходим макрос. Нажмите на ярлыке листа внизу правую кнопку мыши и выберите команду «Просмотреть код». Откроется окно разработчика. В него надо вставить следующий алгоритм.

 Private Sub Worksheet_Change(ByVal Target As Range)
 On Error Resume Next
 If Not Intersect(Target, Range("C2:F2")) Is Nothing And Target.Cells.Count = 1 Then
 Application.EnableEvents = False
 If Len(Target.Offset(1, 0)) = 0 Then
 Target.Offset(1, 0) = Target
 Else
 Target.End(xlDown).Offset(1, 0) = Target
 End If
 Target.ClearContents
 Application.EnableEvents = True
 End If
 End Sub

Обратите внимание, что в строке

 If Not Intersect(Target, Range("E7")) Is Nothing And Target.Cells.Count = 1 Then

Следует проставить адрес ячейки со списком. У нас это будет E7.

Вернитесь на лист Excel и создайте в ячейке E7 список. При выборе значения будут появляться под ним. Следующий код позволит накапливать значения в ячейке.

 Private Sub Worksheet_Change(ByVal Target As Range)
 On Error Resume Next
 If Not Intersect(Target, Range("E7")) Is Nothing And Target.Cells.Count = 1 Then
 Application.EnableEvents = False
 newVal = Target
 Application.Undo
 oldval = Target
 If Len(oldval) <> 0 And oldval <> newVal Then
 Target = Target & "," & newVal
 Else
 Target = newVal
 End If
 If Len(newVal) = 0 Then Target.ClearContents
 Application.EnableEvents = True
 End If
 End Sub

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

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

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

Имеется таблица с данными:

Примеры функции ДВССЫЛ:

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

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

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

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

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

Для выведения четных строк:

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

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

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

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

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

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

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

Функция ДВССЫЛ позволяет преобразовать текст в ссылку Excel

чисел.и будут выступать не«Математические»иВ поле с тем, чтопредставлен в видето она всегда будет

Примеры использования функции ДВССЫЛ в Excel

ссылке дописать оба другие книги) приведет можно получить всюC – сокращенно от появляться число сотрудников. ошибки:

побольше, то Вы столбцу E. КонечнаяФункция«Ноябрь» координаты ячейки, а. Там выбираем наименованиеДВССЫЛ«A1» он ссылается не ссылки на элемент указывать на ячейку доллара сразу (

к возникновению ошибки информацию об абоненте «column» (столбец) –
Если мы используем толькоЗначение аргумента «ссылка на вряд ли захотите
ячейка изменилась, ноINDIRECT, то и результат её содержимое, которое«СУММ»

., так как мы на элемент листа, листа, данные содержащиеся

Как преобразовать число в месяц и транспонировать в Excel

с адресом$C$5 #ССЫЛКА!, если требуемая на основе выбранного указатель столбца; функцию ВПР, появится ячейку» является недопустимой

вписывать в формулу

на начальную это(ДВССЫЛ) имеет вот изменится соответственно. Будет уже имеет текстовый

. Щелкаем по кнопкеПрежде всего, в отдельных работает в обычном а на координаты. в котором нужноC5) — она превратится книга не открыта номера записи (id).X и Y – ошибка: ссылкой – ошибка все числа. Второй не повлияло. такой синтаксис: сложена сумма дохода формат (слово«OK»

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

вне зависимости от в в приложении Excel.Вид исходной таблицы: любые целые положительные

Как преобразовать текст в ссылку Excel?

#ССЫЛКА!. вариант – это=SUM(INDIRECT(«E2»):E8)INDIRECT(ref_text,a1) за указанный период«Март». вносим наименования месяцев поставить значение

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

D2 как ссылкуОбязательный аргумент ссылается на

использовать функцию=СУММ(ДВССЫЛ(«E2»):E8)ДВССЫЛ(ссылка_на_ячейку;a1) времени.). ПолеВслед за выполнением данного начала и конца«ИСТИНА»B4 иметь текстовый вид, пользователя, вставки илии не будет Excel не второй таблицы: строки и столбца на именованный диапазон, другую книгу (является

ROW

Как вставить текст в ссылку на ячейку Excel?

Функцияref_textУрок:«A1» действия запускается окно периода, за который, а можно оставитьсодержит другой элемент то есть, быть

удаления строк и

меняться никак при день, то, наверняка

Для заполнения ячеек новой соответственно. где и находится внешней ссылкой), которая(СТРОКА), как это

INDIRECT(ссылка_на_ячейку) – этоКак посчитать сумму в

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

Особенности использования функции ДВССЫЛ в Excel

листа. Его содержимое «обернута» кавычками.

т.д. Единственная небольшая

любом копировании, т.е.

уже встречали или таблицы данными, соответствующимиФункция ДВССЫЛ может принимать отчет определенного года. недоступна (закрыта) – сделано в формуле(ДВССЫЛ) может создать текст ссылки. Экселе как мы используемСУММ соответственно что мы и теперь формула иАргумент сложность состоит в долларами фиксируются намертво использовали в формулах выбранному из списка текстовые представления ссылок Excel считает значение ошибка #ССЫЛКА!. При массива, введенной в ссылку на именованный
a1Как видим, несмотря на стандартный тип обозначения

, единственной задачей которого«Март» сделаем. Это будут выводит на лист.«A1» том, что если и строка и и функциях Excel абоненту, используем следующую
любого из этих в ячейке текстом. создании подобных формул ячейку D5: диапазон. В этом

– если равен

то, что функцию координат. является суммирование указанныхи равнозначные действия.Данный оператор способен выводитьне является обязательным целевая ячейка пустая,
столбец: ссылки со знаком формулу массива (CTRL+SHIFT+ENTER): двух вариантов представления.Исправить положение помогла функция внешний источник данных
=AVERAGE(LARGE(B1:B8,ROW(1:3))) примере голубые ячейки TRUE (ИСТИНА) илиДВССЫЛПосле того, как адрес значений. Синтаксис этой«Май»После этого щелкаем по в другую ячейку и в подавляющем тоСамый простой и быстрый
доллара, напримерПримечание: перед выполнение формулыПример 1. Преобразовать столбец ДВССЫЛ, которая возвращает должен быть открыт.=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3))) составляют диапазон

exceltable.com⁪>

Пример 1: Создаем не сдвигающуюся начальную ссылку

В первом примере в столбцах C и E находятся одинаковые числа, их суммы, посчитанные при помощи функции SUM (СУММ), тоже одинаковы. Тем не менее, формулы немного отличаются. В ячейке C8 формула вот такая:

В ячейке E8 функция INDIRECT (ДВССЫЛ) создаёт ссылку на начальную ячейку E2:

Если вверху листа вставить строку и добавить значение для января (Jan), то сумма в столбце C не изменится. Изменится формула, отреагировав на прибавление строки:

Однако, функция INDIRECT (ДВССЫЛ) фиксирует E2 как начальную ячейку, поэтому значение января автоматически включается в подсчёт суммы по столбцу E. Конечная ячейка изменилась, но на начальную это не повлияло.

Пример 4: Создаём не сдвигающийся массив чисел

Иногда в формулах Excel необходимо использовать массив чисел. В следующем примере мы хотим получить среднее из 3-х наибольших чисел в столбце B. Числа могут быть вписаны в формулу, как это сделано в ячейке D4:

=AVERAGE(LARGE(B1:B8,{1,2,3}))=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))

Если Вам потребуется массив побольше, то Вы вряд ли захотите вписывать в формулу все числа. Второй вариант – это использовать функцию ROW (СТРОКА), как это сделано в формуле массива, введенной в ячейку D5:

=AVERAGE(LARGE(B1:B8,ROW(1:3)))=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))

Третий вариант – это использовать функцию ROW (СТРОКА) вместе с INDIRECT (ДВССЫЛ), как это сделано с помощью формулы массива в ячейке D6:

=AVERAGE(LARGE(B1:B8,ROW(INDIRECT(“1:3”))))=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ(“1:3”))))

Результат для всех 3-х формул будет одинаковым:

Однако, если сверху листа вставить строки, вторая формула возвратит не верный результат из-за того, что ссылки в формуле изменятся вместе со сдвигом строк. Теперь, вместо среднего значения трёх максимальных чисел, формула возвращает среднее 3-го, 4-го и 5-го по величине чисел.

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

Оцените качество статьи

Нам важно ваше мнение:

Функция ДВССЫЛ Excel — возможные ошибки и проблемы

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

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

Ошибка #ССЫЛКА!

Чаще всего функция ДВССЫЛ возвращает ошибку #ССЫЛКА! в следующих случаях:

Аргумент ссылка_на_ячейку не является допустимой ссылкой Excel. Если вы пытаетесь передать функции текст, который не может обозначать ссылку на ячейку (например, «A1B0»), то формула приведет к ошибке #ССЫЛКА!. Во избежание возможных проблем проверьте аргументы функции ДВССЫЛ .
Превышен предел размера диапазона. Если аргумент ссылка_на_ячейку вашей формулы ДВССЫЛ ссылается на диапазон ячеек за пределами строки 1 048 576 или столбца 16 384, вы также получите ошибку #ССЫЛКА в Excel 2007 и новее. Более ранние версии Excel игнорируют превышение этого лимита и действительно возвращают некоторое значение, хотя часто не то, что вы ожидаете.
Используемый в формуле лист или рабочая книга закрыты.Если ваша формула с ДВССЫЛ адресуется на другую книгу или лист Excel, то эта другая книга или электронная таблица должны быть открыты, иначе ДВССЫЛ возвращает ошибку #ССЫЛКА! . Впрочем, это требование характерно для всех формул, которые ссылаются на другие рабочие книги Excel.

Ошибка из-за несовпадения региональных настроек.

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

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

В стандартной конфигурации Windows для Северной Америки и некоторых других стран разделителем списка по умолчанию является запятая.

В результате при копировании формулы между двумя разными языковыми стандартами Excel вы можете получить сообщение об ошибке « Мы обнаружили проблему с этой формулой… », поскольку разделитель списка, используемый в формуле, отличается от того, что установлен на вашем компьютере. Если вы столкнулись с этой ошибкой при копировании какой-либо НЕПРЯМОЙ формулы из этого руководства в Excel, просто замените все запятые (,) точками с запятой (;) (либо наоборот). В обычных формулах Excel эта проблема, естественно, не возникнет. Там Excel сам поменяет разделители исходя из ваших текущих региональных настроек.

Чтобы проверить, какие разделитель списка и десятичный знак установлены на вашем компьютере, откройте панель управления и перейдите в раздел «Регион и язык» > «Дополнительные настройки».

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

Вот еще несколько статей по той же теме:

Связанный список

У пользователей также есть возможность создавать и более сложные взаимозависимые списки (связанные). Это значит, что список в одной ячейке будет зависеть от того, какое значение мы выбрали в другой. Например, в единицах измерения товара мы можем задать килограммы или литры. Если вы выберем в первой ячейке кефир, во второй на выбор будет предложено два варианта – литры или миллилитры. А если в первую ячейки мы остановимся на яблоках, во второй у нас будет выбор из килограммов или граммов.

Для этого нужно подготовить как минимум три столбца. В первом будут заполнены наименования товаров, а во втором и третьем – их возможные единицы измерения. Столбцов с возможными вариациями единиц измерения может быть и больше.
Сначала создаем один общий список для всех наименований продуктов, выделив все строки столбца “Наименование”, через контекстное меню выделенного диапазона.
Задаем ему имя, например, “Питание”.
Затем таким же образом формируем отдельные списки для каждого продукта с соответствующими единицами измерения. Для большей наглядности возьмем в качестве примера первую позицию – “Лук”. Отмечаем ячейки, содержащие все единицы измерения для этого продукта, через контекстное меню присваиваем имя, которое полностью должно совпадать с наименованием.Таким же образом создаем отдельные списки для всех остальных продуктов в нашем перечне.
После этого вставляем общий список с продуктами в верхнюю ячейку первого столбца основной таблицы – как и в описанном выше примере, через кнопку “Проверка данных” (вкладка “Данные”).
В качестве источника указываем “=Питание” (согласно нашему названию).
Затем кликаем по верхней ячейке столбца с единицами измерения, также заходим в окно проверки данных и в источнике указываем формулу “=ДВССЫЛ(A2)“, где A2 – номер ячейки с соответствующим продуктом.
Списки готовы. Осталось его только растянуть их все строки таблицы, как для столбца A, так и для столбца B.

Что такое именованный диапазон ячеек в Excel?

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

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

Для имен действует ряд ограничений:

– имя может содержать до 255 символов;

– первым символом в имени должна быть буква, знак подчеркивания (_) либо обратная косая черта (), остальные символы имени могутбыть буквами, цифрами, точками и знаками подчеркивания;

– имена не могут быть такими же, как ссылки на ячейки;

– пробелы в именах не допускаются;

– строчные и прописные буквы не различаются.

Управление существующими именованными диапазонами (создание, просмотр и изменение) можно осуществлять при помощи диспетчера имен. В Excel 2007 диспетчер находится на вкладке “Формулы”, в группе кнопок “Определенные имена”.

Выделение диапазонов

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

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

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

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

Сравнение диапазонов

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

Например, для построчного сравнения часто используется логическая функция “ЕСЛИ” и какой-либо из операторов сравнения (также можно использовать и другие функции, например “СЧЕТЕСЛИ” из категории статистические для проверки вхождения элементов одного списка в другой).

Также для поиска отличий по столбцам или по строкам используется стандартное средство Excel, которое находится на вкладке “Главная”, в группе кнопок “Редактирование”, в меню кнопки “Найти и выделить”. Если в этом меню выбрать пункт “Перейти” и далее нажать кнопку “Выделить”, то в диалоговом окне “Выделение группы ячеек” можно выбрать одну из опций “Отличия по строкам” или “Отличия по столбцам”.

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

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

Абсолютные и относительные ссылки в excel

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

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

Сегодня в статье разберемся, как сделать абсолютные и относительные ссылки в excel.

Относительные ссылки

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

Отсюда следует название. При копировании ссылки автоматически изменяются, что позволяет за один раз провести расчеты для всей таблицы. Рассмотрим пример.

Есть список фруктов, проданных за день, необходимо посчитать выручку.

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

В ячейке D2 столбца Выручка записываете формулу В2*С2.

Нажимаете Enter и видите результат.

Используя маркер автозаполнения, применяете формулу к остальным строкам. Эта операция позволяет автоматически скопировать формулу.

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

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

Абсолютные ссылки

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

Внесем новые данные в таблицу

Запишем формулу =E2-E2*$D$2

Копируем во все остальные строки.

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

D$2 — фиксируется только строка.
$D2 — закрепляется только столбец.
$D$2 — неизменными остаются и столбец, и строка.

Первые две еще называются смешанными адресами.

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

Ссылки между листами

Известно, что одна рабочая книга excel по умолчанию содержит три листа. Редактор позволяет делать ссылки и между ними. Для этого достаточно в формуле после названия листа поставить восклицательный знак, а затем указать ячейку, на которую ссылаетесь. Выглядит это следующим образом: =A17*Лист2!B4

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

Их использование значительно ускоряет обработку данных и вычисления внутри таблицы

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

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

Отметим, что принцип создания ссылок одинаковый для всех версий редактора от Microsoft, в частности для серий 2007, 2010, 2013 и 2016 годов.

Внешняя ссылка на закрытую книгу

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

Но когда он закрыт, Excel не знает, где искать ячейки / диапазон, на которые вы ссылались.

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

Ниже приведена ссылка на ячейку A1 на листе Sheet1 в книге Example File. Поскольку этот файл не открыт, он также относится к месту, где файл сохранен.

= 'C: \ Users \ sumit \ Desktop \  Sheet1'! $ A $ 1

Приведенная выше ссылка состоит из следующих частей:

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

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

Влияние изменения расположения файла на ссылки

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

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

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

Когда вы нажмете «Обновить», отобразится еще одно приглашение, в котором вы можете выбрать параметры для редактирования ссылок (при этом появится диалоговое окно, показанное ниже).

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

Отправка писем через Excel по гиперссылке

Форма выглядит следующим образом:

Значения ячеек B3 и B4 могут быть изменены на усмотрение пользователя (в зависимости от причины обращения в службу поддержки). В ячейке B5 записана функция:

Данная функция выполняет конкатенацию (сцепление текстовых строк, принимаемых в качестве параметров).

mailto: — команда отправки письма;
B2 – ячейка, содержащая email службы поддержки;
«?subject=» – команда для записи темы письма;
КОДИР.URL(B3) – функция, которая выполняет преобразование текста темы письма в кодировку URL;
«&body=» – команда для записи текста письма;
КОДИР.URL(B4) – текст письма в кодировке URL.

В ячейке B6 записана следующая функция:

B5 – URL-команда на отправку письма, содержащая тему и текст письма;
«Отправить письмо» – имя гиперссылки.

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

Текстовые функции

ЛЕВСИМВ, ПРАВСИМВ и ПСТР

Чтобы извлечь символы слева, справа или из середины текста, используйте функции ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT) и ПСТР (MID).

ДЛСТР

Функция ДЛСТР (LEN) возвращает длину текстовой строки. ДЛСТР (LEN) используется во многих формулах, которые считают слова или символы.

НАЙТИ и ПОИСК

Чтобы найти определенный текст в ячейке, используйте функцию НАЙТИ (FIND) или ПОИСК (SEARCH). Эти функции возвращают числовую позицию совпадающего текста, но ПОИСК (SEARCH) позволяет использовать подстановочные знаки, а НАЙТИ (FIND) учитывает регистр. Обе функции выдают ошибку, когда текст не найден, поэтому оберните в функцию ЕЧИСЛО (ISNUMBER), чтобы вернуть ИСТИНА или ЛОЖЬ.

ЗАМЕНИТЬ и ПОДСТАВИТЬ

Чтобы заменить часть текста с определенной позиции, используйте функцию ЗАМЕНИТЬ (REPLACE). Чтобы заменить конкретный текст новым значением, используйте функцию ПОДСТАВИТЬ (SUBSTITUTE). В первом примере ЗАМЕНИТЬ (REPLACE) удаляет две звездочки (**), заменяя первые два символа пустотой («»). Во втором примере ПОДСТАВИТЬ (SUBSTITUTE) удаляет все хеш-символы (#), заменяя «#» на «».

КОДСИМВ и СИМВОЛ

Чтобы выяснить числовой код символа, используйте функцию КОДСИМВ (CODE). Чтобы перевести числовой код обратно в символ, используйте функцию СИМВОЛ (CHAR). В приведенном ниже примере КОДСИМВ (CODE) переводит каждый символ в столбце B в соответствующий код. В столбце F СИМВОЛ (CHAR) переводит код обратно в символ.

ПЕЧСИМВ и СЖПРОБЕЛЫ

Чтобы избавиться от лишнего пространства в тексте, используйте функцию СЖПРОБЕЛЫ (TRIM). Чтобы удалить разрывы строк и другие непечатаемые символы, используйте ПЕЧСИМВ (CLEAN).

СЦЕП, СЦЕПИТЬ и ОБЪЕДИНИТЬ

В Excel 2016 Office 365 появились новые функции СЦЕП (CONCAT) и ОБЪЕДИНИТЬ (TEXTJOIN). Функция СЦЕП (CONCAT) позволяет объединять несколько значений, включая диапазон значений без разделителя. Функция ОБЪЕДИНИТЬ (TEXTJOIN) делает то же самое, но позволяет вам указать разделитель, а также может игнорировать пустые значения.

Excel также предоставляет функцию СЦЕПИТЬ (CONCATENATE). Еще можно воспользоваться непосредственно символом амперсанда (&) в формуле.

СОВПАД

Функция СОВПАД (EXACT) позволяет сравнивать две текстовые строки с учетом регистра.

ПРОПИСН, СТРОЧН и ПРОПНАЧ

Чтобы изменить регистр текста, используйте функции ПРОПИСН (UPPER), СТРОЧН (LOWER) и ПРОПНАЧ (PROPER).

ТЕКСТ

И последнее, но не менее важное — это функция ТЕКСТ (TEXT). Текстовая функция позволяет применять форматирование чисел (включая даты, время и т.д.) как текст

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

Разбор функции ДВССЫЛ (INDIRECT) на примерах

На первый взгляд (особенно при чтении справки) функция ДВССЫЛ (INDIRECT) выглядит простой и даже ненужной. Ее суть в том, чтобы превращать текст похожий на ссылку – в полноценную ссылку. Т.е. если нам нужно сослаться на ячейку А1, то мы можем либо привычно сделать прямую ссылку (ввести знак равно в D1, щелкнуть мышью по А1 и нажать Enter), а можем использовать ДВССЫЛ для той же цели:

Обратите внимание, что аргумент функции – ссылка на А1 – введен в кавычках, поэтому что, по сути, является здесь текстом. “Ну ОК”, – скажете вы. “И что тут полезного?”

“И что тут полезного?”

“Ну ОК”, – скажете вы. “И что тут полезного?”.

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

Пример 1. Транспонирование

пазон в горизонтальный (транспонировать). Само-собой, можно использовать специальную вставку или функцию ТРАНСП (TRANSPOSE) в формуле массива, но можно обойтись и нашей ДВССЫЛ:

Логика проста: чтобы получить адрес очередной ячейки, мы склеиваем спецсимволом “&” букву “А” и номер столбца текущей ячейки, который выдает нам функция СТОЛБЕЦ (COLUMN) .

Обратную процедуру лучше проделать немного по-другому. Поскольку на этот раз нам нужно формировать ссылку на ячейки B2, C2, D2 и т.д., то удобнее использовать режим ссылок R1C1 вместо классического “морского боя”. В этом режиме наши ячейки будут отличаться только номером столбца: B2=R1C 2 , C2=R1C 3 , D2=R1C 4 и т.д.

Тут на помощь приходит второй необязательный аргумент функции ДВССЫЛ. Если он равен ЛОЖЬ (FALSE) , то можно задавать адрес ссылки в режиме R1C1. Таким образом, мы можем легко транспонировать горизонтальный диапазон обратно в вертикальный:

Пример 2. Суммирование по интервалу

Мы уже разбирали один способ суммирования по окну (диапазону) заданного размера на листе с помощью функции СМЕЩ (OFFSET) . Подобную задачу можно решить и с помощью ДВССЫЛ. Если нам нужно суммировать данные только из определенного диапазона-периода, то можно склеить его из кусочков и превратить затем в полноценную ссылку, которую и вставить внутрь функции СУММ (SUM) :

Пример 3. Выпадающий список по умной таблице

Иногда Microsoft Excel не воспринимает имена и столбцы умных таблиц как полноценные ссылки. Так, например, при попытке создать выпадающий список (вкладка Данные – Проверка данных) на основе столбца Сотрудники из умной таблицы Люди мы получим ошибку:

Если же “обернуть” ссылку нашей функцией ДВССЫЛ, то Excel преспокойно ее примет и наш выпадающий список будет динамически обновляться при дописывании новых сотрудников в конец умной таблицы:

Пример 4. Несбиваемые ссылки

Как известно, Excel автоматически корректирует адреса ссылок в формулах при вставке или удалении строк-столбцов на лист. В большинстве случаев это правильно и удобно, но не всегда. Допустим, что нам нужно перенести имена из справочника по сотрудникам в отчет:

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

Пример 5. Сбор данных с нескольких листов

Предположим, что у нас есть 5 листов с однотипными отчетами от разных сотрудников (Михаил, Елена, Иван, Сергей, Дмитрий):

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

Собрать данные со всех листов (не просуммировать, а положить друг под друга “стопочкой”) можно всего одной формулой:

Как видите, идея та же: мы склеиваем ссылку на нужную ячейку заданного листа, а ДВССЫЛ превращает ее в “живую”. Для удобства, над таблицей я добавил буквы столбцов (B,C,D), а справа – номера строк, которые нужно взять с каждого листа.

Подводные камни

При использовании ДВССЫЛ (INDIRECT) нужно помнить про ее слабые места:

Если вы делаете ссылку в другой файл (склеивая имя файла в квадратных скобках, имя листа и адрес ячейки), то она работает только пока исходный файл открыт. Если его закрыть, то получим ошибку #ССЫЛКА!
С помощью ДВССЫЛ нельзя сделать ссылку на динамический именованный диапазон. На статический – без проблем.
ДВССЫЛ является волатильной (volatile) или “летучей” функцией, т.е. она пересчитывается при любом изменении любой ячейки листа, а не только влияющих ячеек, как у обычных функций. Это плохо отражается на быстродействии и на больших таблицах ДВССЫЛ лучше не увлекаться.

Активный объект

В хоть какой момент времени в Excel есть активный объект Workbook – это рабочая книжка, открытая в этот момент. Буквально так же существует активный объект Worksheet, активный объект Range и так дальше.

Сослаться на активный объект Workbook либо Sheet в коде VBA можно как на ActiveWorkbook либо ActiveSheet, а на активный объект Range – как на Selection.

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

Таковым образом, чтоб сослаться на спектр A1:B10 на активном рабочем листе активной книжки, можно записать просто:

Смена активного объекта

Если в процессе выполнения программки требуется создать активной другую рабочую книжку, иной рабочий лист, спектр и так дальше, то для этого необходимо применять способы Activate либо Select вот таковым образом:

Способы объектов, в том числе использованные лишь что способы Activate либо Select, дальше будут рассмотрены наиболее тщательно.