Вывод в excel в указанную ячейку

Всем доброго времени суток!)

Я из MS Access создаю Excel-файл. На первом листе создаются и заполняются 2 таблицы (данные для них берутся из БД). Затем на основе первой таблицы создается круговая диаграмма, а на основе второй — линейная, гистограмма и график.
Проблема в том, что у круговой диаграммы съезжают подписи данных:

А у остальных диаграмм, например у гистограммы, съезжает «Легенда»:

Причем! Если данные из таблицы, которые попадают в «Легенду диаграммы», стереть и перебить их вручную, то этот сдвиг пропадает и все ОК.

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

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

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

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

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

=ДВССЫЛ(ссылка_на_ячейку;[a1])

Таким образом, формула имеет всего два аргумента.

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

Аргумент «A1» не является обязательным и в подавляющем большинстве случаев его вообще не нужно указывать. Он может иметь два значения «ИСТИНА» и «ЛОЖЬ». В первом случае оператор определяет ссылки в стиле «A1», а именно такой стиль включен в Excel по умолчанию. Если значение аргумента не указывать вовсе, то оно будет считаться именно как «ИСТИНА». Во втором случае ссылки определяются в стиле «R1C1». Данный стиль ссылок нужно специально включать в настройках Эксель.

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

=ДВССЫЛ("A1")

будет эквивалентно выражению

=A1

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

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

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

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

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

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

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

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

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

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



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



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

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

   После этого щелкаем по кнопке «OK».



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



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

Урок: Мастер функций в программе Excel

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

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

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

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



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

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



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



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



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



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



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

   =СУММ(число1;число2;…)

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

   Устанавливаем курсор в поле «Число1». Затем жмем на пиктограмму в виде перевернутого треугольника справа от поля наименования диапазонов. Раскрывается список последних используемых функций. Если среди них присутствует наименование «ДВССЫЛ», то сразу кликаем по нему для перехода в окно аргументов данной функции. Но вполне может быть, что в этом списке вы его не обнаружите. В таком случае нужно щелкнуть по наименованию «Другие функции…» в самом низу списка.



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



9. Происходит запуск окна аргументов оператора ДВССЫЛ. В поле «Ссылка на ячейку» указываем адрес элемента листа, который содержит наименование начального месяца диапазона предназначенного для расчета суммы. Обратите внимание, что как раз в этом случае брать ссылку в кавычки не нужно, так как в данном случае в качестве адреса будут выступать не координаты ячейки, а её содержимое, которое уже имеет текстовый формат (слово «Март»). Поле «A1» оставляем пустым, так как мы используем стандартный тип обозначения координат.

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



10. После этого мы возвращаемся в окно аргументов СУММ. Как видим, в поле «Число1» уже отобразился оператор ДВССЫЛ со своим содержимым. Устанавливаем курсор в это же поле сразу после последнего символа в записи. Ставим знак двоеточия (:). Данный символ означает знак адреса диапазона ячеек. Далее, не извлекая курсор из поля, опять кликаем по значку в виде треугольника для выбора функций. На этот раз в списке недавно использованных операторов наименование «ДВССЫЛ» должно точно присутствовать, так как мы совсем недавно использовали эту функцию. Щелкаем по наименованию.



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



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



13. Если мы поменяем в ячейках, где вписаны наименования месяцев начала и конца расчетного периода, на другие, например на «Июнь» и «Ноябрь», то и результат изменится соответственно. Будет сложена сумма дохода за указанный период времени.

Урок: Как посчитать сумму в Экселе

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