Как сделать ссылку на таблицу в excel?

Гиперссылка в Excel. Как сделать гиперссылку в Экселе

Гиперссылки широко используются в Интернете для навигации по сайтам и документам. Работая с файлами Excel вы также можете создавать гиперссылки, как на интернет-ресурсы, так и на ячейки, файлы или форму отправку Email.

Что такое гиперссылка

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

Excel позволяет создавать гиперссылки для:

  • Перехода в определенное место в текущей книге;
  • Открытия другого документа или перехода к определенному месту в этом документе, например лист в файле Excel или закладке в документе Word;
  • Перехода на веб страницу в Интернете;
  • Создания нового файла Excel;
  • Отправки сообщения электронной почты по указанному адресу.

Гиперссылку в Excel легко заметить, она выглядит как подчеркнутый текст, выделенный синим цветом:

Абсолютные и относительные гиперссылки в Excel

В Excel существует два типа гиперссылок: абсолютные и относительные.

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

Абсолютные гиперссылки содержат в себе полный интернет адрес или полный путь на компьютере. Например:

Относительные гиперссылки

Относительные ссылки содержат в себе частичный путь, например:

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

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

Чтобы создать гиперссылку проделайте следующие шаги:

  • Выделите ячейку, в которой вы хотите создать гиперссылку;
  • Нажмите правую клавишу мыши;
  • В выпадающем меню выберите пункт “Ссылка”:

  • В диалоговом окне выберите файл или введите веб-адрес ссылки в поле “Адрес”:

Ниже, мы подробней разберем как создать гиперссылку:

  • На другой документ;
  • На веб-страницу;
  • На конкретную область в текущем документе;
  • На новую рабочую книгу Excel;
  • На окно отправки Email.

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

Чтобы указать гиперссылку на другой документ, например Excel, Word или Powerpoint файлы:

  • Откройте диалоговое окно для создания гиперссылки;
  • В разделе “Связать с” выберите “Файлом, веб-страницей”;
  • В поле “Искать в” выберите папку, где лежит файл, на который вы хотите создать ссылку;
  • В поле “Текст” введите текст, который будет отображаться в качестве ссылки;
  • Нажмите “ОК”.

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

Как создать гиперссылку в Excel на веб-страницу

Чтобы указать гиперссылку веб-страницу:

  • Откройте диалоговое окно для создания гиперссылки;
  • В разделе “Связать с” выберите пункт “Файлом, веб-страницей”;
  • Нажмите на кнопку “Интернет”;
  • Введите адрес веб-страницы в поле “Адрес”;
  • В поле “Текст” укажите текст, отображаемый в виде ссылки.

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

Для создания гиперссылки на конкретный лист текущего файла Excel или ячейки:

  • Откройте диалоговое окно для создания гиперссылки;
  • В левой колонке диалогового окна под надписью “Связать с” выберите “Файлом, веб-страницей”;
  • В диалоговом окне нажмите кнопку “Закладка…” и выберите лист создания ссылки. В поле “Введите адрес ячейки” укажите ячейку.

Как создать гиперссылку в Excel на новую рабочую книгу

Для вставки гиперссылки, после нажатия на которую будет создан новый Excel-файл:

  • Откройте диалоговое окно для создания гиперссылки;
  • В левой колонке диалогового окна под надписью “Связать с” выберите “Новый документ”;
  • В поле “Текст” укажите текст ссылки;
  • В поле “Имя нового документа” укажите название нового Excel файла;
  • В поле “Путь” укажите место хранения, где будет сохранен новый файл;
  • В поле “Когда вносить правку в новый документ” укажите настройку, когда следует приступить к редактированию нового файла после нажатия ссылки.
  • Нажмите кнопку “ОК”

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

Для вставки гиперссылки, после нажатия на которую будет создан e-mail:

  • Откройте диалоговое окно для создания гиперссылки;
  • В левой колонке диалогового окна под надписью “Связать с” выберите “Электронная почта”;
  • В поле “Текст” укажите текст ссылки;
  • В поле “Адрес эл. почты” укажите E-mail адрес, куда будет отправлено письмо;
  • В поле “Тема” укажите тему создаваемого письма;
  • Нажмите кнопку “ОК”

Как редактировать гиперссылку в Excel

Для редактирования уже созданной гиперссылки, кликните по ячейке со ссылкой правой клавишей мыши и в выпадающем меню выберите “Edit Hyperlink”.

В диалоговом окне внесите корректировки в ссылку.

Как отформатировать гиперссылку в Excel

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

  • Перейдите на вкладку панели инструментов “Главная”, затем в раздел “Стили ячеек”:

  • Кликните на “Гиперссылка” правой кнопкой мыши и выберите пункт “Изменить” для редактирования формата ссылки:

  • Кликните на “Открывавшаяся гиперссылка” правой кнопкой мы и выберите пункт “Изменить” для редактирования формата ссылки;
  • В диалоговом окне “Стили” нажмите кнопку “Формат”:

  • в диалоговом окне “Format Cells” перейдите на вкладки “Шрифт” и/или “Заливка” для настройки формата ссылок:

Как удалить гиперссылку в Excel

Удаление гиперссылки осуществляется в два клика:

  • Нажмите правой клавишей мыши на ячейки со ссылкой;
  • В выпадающем меню выберите пункт “Удалить гиперссылку”.

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

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

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

Прямая ссылка на ячейки

Имена таблицы и столбцов в Excel

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

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

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

Читать еще:  Как сделать формулы в excel с разных листов?

Sales (продажи ) Пользователь

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

Чтобы создать таблицу, выделите любую ячейку в диапазоне данных и нажмите клавиши CTRL + T.

Убедитесь в том, что флажок таблица с заголовками установлен, и нажмите кнопку ОК.

В ячейке E2 введите знак равенства (=), а затем щелкните ячейку C2.

В строке формул после знака равенства появится структурированная ссылка [@[ОбъемПродаж]].

Введите звездочку (*) сразу после закрывающей скобки и щелкните ячейку D2.

В строке формул после звездочки появится структурированная ссылка [@[ПроцентКомиссии]].

Нажмите клавишу ВВОД.

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

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

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

На листе примера щелкните ячейку E2.

В строке формул введите = C2 * D2 и нажмите клавишу Ввод.

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

Как изменить имя таблицы?

При создании таблицы Excel ей назначается имя по умолчанию («Таблица1», «Таблица2» и т. д.), но его можно изменить, чтобы сделать более осмысленным.

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

Введите нужное имя в поле имя таблицы и нажмите клавишу Ввод.

В этом примере мы используем имя ОтделПродаж.

При выборе имени таблицы соблюдайте такие правила:

Использование допустимых символов Имя должно начинаться с буквы, знака подчеркивания (_) или обратной косой черты (). Используйте буквы, цифры, точки и знаки подчеркивания для остального имени. Вы не можете использовать «C», «c», «R» или «r» для имени, поскольку они уже назначены как сочетание для выбора столбца или строки активной ячейки при их вводе в поле » имя » или » Перейти «.

Не используйте ссылки на ячейки Имена не могут совпадать с ссылками на ячейки, например Z $100 или R1C1.

Не используйте пробелы для разделения слов В имени нельзя использовать пробелы. В качестве разделителей слов можно использовать символ подчеркивания (_) и точку (.). Например, ОтделПродаж, Салес_такс или First. Quarter.

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

Присваивайте таблицам уникальные имена. Повторяющиеся имена запрещены. Excel не делает различий между символами верхнего и нижнего регистра в именах. Например, если в книге уже есть таблица «ПРОДАЖИ», то при попытке присвоить другой таблице имя «Продажи» вам будет предложено выбрать уникальное имя.

Использование идентификатора объекта Если вы планируете использовать смешанные таблицы, сводные таблицы и диаграммы, рекомендуется присвоить имена типу объекта. Например: Тбл_салес для таблицы продаж, Пт_салес для сводной таблицы продаж и Чрт_салес для диаграммы продаж или Птчрт_салес для сводной диаграммы продаж. Это позволит сохранять все имена в упорядоченном списке в диспетчере имен.

Правила синтаксиса структурированных ссылок

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

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

Имя таблицы: ОтделПродаж — это имя пользовательской таблицы. Он ссылается на табличные данные без заголовков или строк итогов. Вы можете использовать имя таблицы по умолчанию, например Таблица1, или изменить его, чтобы использовать другое имя.

Указатель столбца: [Сумма продаж]а[Сумма комиссии] — это описатели столбцов, которые используют имена столбцов, которые они представляют. Они ссылаются на данные столбца без заголовка столбца или строки итогов. Всегда заключайте спецификаторы в квадратные скобки, как показано ниже.

Указатель элемента: [#Totals] и [#Data] — это указатели специальных элементов, которые указывают на определенные части таблицы, такие как строка итогов.

Указатель таблицы. [[#Итого],[ОбъемПродаж]] и [[#Данные],[ОбъемКомиссии]] — это указатели таблицы, которые представляют внешние части структурированной ссылки. Внешняя часть следует за именем таблицы и заключается в квадратные скобки.

Структурированная ссылка: (ОтделПродаж [[#Totals]; [сумма продаж]] и отделпродаж [[#Data]; [сумма комиссии]] — структурированные ссылки, представленные в виде строки, начинающейся с имени таблицы и заканчивающейся указателем столбца.

При создании или изменении структурированных ссылок вручную учитывайте перечисленные ниже правила синтаксиса.

Использование квадратных скобок для указателей Все описатели таблиц, столбцов и специальных элементов должны быть заключены в парные квадратные скобки ([]). Спецификатор, который содержит другие описатели, требует наличия внешних парных скобок для заключения внутренних парных скобок других описателей. Пример: = ОтделПродаж [[продавец]: [регион]]

Все заголовки столбцов — это текстовые строки. Но при использовании в структурированной ссылке их не нужно заключать в кавычки. Числа или даты, например 2014 или 01.01.2014, также считаются текстовыми строками. Нельзя использовать выражения с заголовками столбцов. Например, выражение ОтделПродажСводкаФГ[[2014]:[2012]] недопустимо.

Заключайте в квадратные скобки заголовки столбцов, содержащие специальные знаки. Если присутствуют специальные знаки, весь заголовок столбца должен быть заключен в скобки, а это означает, что для указателя столбца потребуются двойные скобки. Пример: =ОтделПродажСводкаФГ[[Итого $]]

Дополнительные скобки в формуле нужны при наличии таких специальных знаков:

левая квадратная скобка ([);

правая квадратная скобка (]);

левая фигурная скобка (<);

правая фигурная скобка (>);

Операторы ссылок

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

Эта структурированная ссылка:

Все ячейки в двух или более смежных столбцах

: (двоеточие) — оператор ссылки

Сочетание двух или более столбцов

, (запятая) — оператор объединения

Пересечение двух или более столбцов

(пробел) — оператор пересечения

Указатели специальных элементов

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

Этот указатель специального элемента:

Вся таблица, включая заголовки столбцов, данные и итоги (если они есть).

Только строки данных.

Только строка заголовка.

Только строка итога. Если ее нет, будет возвращено значение null.

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

Excel автоматически заменяет указатели «#Эта строка» более короткими указателями @ в таблицах, содержащих больше одной строки данных. Но если в таблице только одна строка, Excel не заменяет указатель «#Эта строка», и это может привести к тому, что при добавлении строк вычисления будут возвращать непредвиденные результаты. Чтобы избежать таких проблем при вычислениях, добавьте в таблицу несколько строк, прежде чем использовать формулы со структурированными ссылками.

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

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

Читать еще:  Как сделать связанные выпадающие списки в excel легко?

Тип структурированной ссылки

Перемножает соответствующие значения из текущей строки.

Перемножает соответствующие значения из каждой строки обоих столбцов.

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

Примеры использования структурированных ссылок

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

Эта структурированная ссылка:

Все ячейки в столбце «ОбъемПродаж».

Заголовок столбца «ПроцентКомиссии».

Итог столбца «Регион». Если нет строки итогов, будет возвращено значение ноль.

Все ячейки в столбцах «ОбъемПродаж» и «ПроцентКомиссии».

Только данные в столбцах «ПроцентКомиссии» и «ОбъемКомиссии».

Только заголовки столбцов от «Регион» до «ОбъемКомиссии».

Итоги столбцов от «ОбъемПродаж» до «ОбъемКомиссии». Если нет строки итогов, будет возвращено значение null.

Только заголовок и данные столбца «ПроцентКомиссии».

=ОтделПродаж[[#Эта строка], [ОбъемКомиссии]]

Ячейка на пересечении текущей строки и столбца сумма комиссии. При использовании в той же строке, что и строка заголовка или итог, возвращается ошибка #VALUE! .

Если ввести длинную форму этой структурированной ссылки (#Эта строка) в таблице с несколькими строками данных, Excel автоматически заменит ее укороченной формой (со знаком @). Две эти формы идентичны.

E5 (если текущая строка — 5)

Методы работы со структурированными ссылками

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

Использование автозавершения формул. Автозавершение формул может пригодиться при вводе структурированных ссылок для соблюдения правил синтаксиса. Дополнительные сведения см. в статье Использование автозавершения формул.

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

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

Преобразование диапазона в таблицу и таблицы в диапазон При преобразовании таблицы в диапазон все ссылки на ячейки изменяются на эквивалентные абсолютные ссылки на стиль a1. При преобразовании диапазона в таблицу Excel не автоматически изменяет ссылки на ячейки этого диапазона на эквивалентные структурированные ссылки.

Отключение заголовков столбцов Вы можете включать и выключать заголовки столбцов таблицы с помощью вкладки » конструктор таблиц» _гт_ строки заголовков. Если отключить заголовки столбцов таблицы, структурированные ссылки, использующие имена столбцов, не будут затронуты и их можно использовать в формулах. Структурированные ссылки, которые ссылаются непосредственно на заголовки таблицы (например, = ОтделПродаж [[#Headers], [% комиссионн]]), будут приводить к #REFу.

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

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

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

Примечание: Копирование структурированной ссылки и заполнение структурированной ссылки — не то же самое. При копировании все структурированные ссылки остаются неизменными, а при заполнении формулы полные структурированные ссылки настраивают указатели столбцов, например ряды, как показано в таблице ниже.

Excel: как делать ссылки на сводную таблицу

Я поясню ее на примере отчета, фрагмент которого показан на рис. 1. Это сводная таблица, которая сформирована по некоторой базе данных. В таблице показаны объемы продаж по шести контрагентам. Предположим, что эти данные мы решили вставить в другую таблицу в виде ссылок на ячейки сводного отчета, и уже там сделать окончательный расчет. Посмотрим, что из этого получится. Чтобы не усложнять задачу, я создам ссылки на том же рабочем листе, где расположена сводная таблица. Дальше делаем так:

1. Становимся на свободную ячейку, пусть это будет « D3 ».

2. Набираем символ «=» (начало формулы).

3. Щелкаем левой кнопкой мыши на ячейке « B3 » (я хочу сделать ссылку на сумму реализации по контрагенту « ТОВ «Топаз» »). В ячейке « D3 » вместо ссылки мы увидим такой результат: « =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Сумма»; $A$1;»Покупатель»;»ТОВ «»Топаз»»») ». При этом значение в ячейке « D3 » будет равно « 119,80 », что соответствует объемам продаж по « ТОВ «Топаз» ».

4. Копируем эту формулу вниз до ячейки « D8 » (на всю высоту сводной таблицы). Результат во всех ячейках будет одинаковым — « 119,80 ». То есть функция получения данных из сводного отчета сослалась на одну и ту же ячейку сводной таблицы.

Причина такого поведения лежит в параметрах функции « =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() ». Таких параметров у нее четыре. Первым идет название поля, по которому нужно выбрать итог. В нашем случае это поле « Сумма ». Так это поле называлось в исходной базе, с этим именем оно и попало в сводный отчет. Вторым параметром стоит ссылка на ячейку с заголовком поля. В формуле эта ссылка выглядит как « $A$1 ». Кстати, абсолютная адресация в данном случае обязательна! Третий параметр — название поля, по которому Excel будет выбирать данные из сводного отчета. В формуле указано, что поиск конкретного числа в сводной таблице нужно делать по полю « Покупатель ». Последний параметр — это строка для поиска конкретного значения среди покупателей. В нашей функции указано значение « ТОВ «Топаз» ». Поэтому Excel выберет итог именно по этому контрагенту. Сразу бросается в глаза, что большинство параметров в функции « =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() » указаны в виде текстовых строк. Именно поэтому не сработала корректировка адресов при копировании формулы в ячейки « D3:D8 », и все функции вернули один и тот же результат.

Кстати, исправить такую ситуацию несложно: нужно вместо фиксированного элемента « «ТОВ «»Топаз»»» » поставить ссылку на ячейку « A3 ». То есть формула в ячейке « D3 » должна выглядеть так: « =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ («Сумма»;$A$1;»Покупатель» ; A3 ) » (изменения выделены полужирным начертанием). В этом варианте после копирования формулы вниз до ячейки « D8 » мы получим правильные объемы реализации по каждому контрагенту.

Читать еще:  Как сделать структуру сайта в excel?

Однако речь сейчас о другом. Использование функции « =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() » имеет свои преимущества и недостатки. Среди преимуществ я бы указал, что независимо от порядка сортировки записей в сводной таблице ссылка через функцию обеспечит правильный результат. И это понятно — извлечение данных из сводного отчета функция делает по ключевому полю, а не по адресу рабочего листа! Если посмотреть на формулу в ячейке « D3 », то ключевым полем для обращения к сводной таблице является название фирмы « ТОВ «Топаз» ». И при этом не имеет никакого значения, где конкретно находится запись по этой фирме — на первой позиции отчета или в самом конце. Данные Excel подставит правильно.

Недостаток работы с функцией « =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() » состоит в том, что нужно корректировать значение ключевого поля или заменять его ссылкой. Поэтому в некоторых случаях удобнее вместо встроенной функции использовать ссылки на ячейки сводной таблицы. Чтобы вставить такие ссылки автоматически (отказаться от использования функции « =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() »), нужно знать одну тонкость.

Секрет Встроенную функцию « =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() » Excel использует только при ссылках на поля в области данных сводного отчета. При организации ссылок на заголовки строк или колонок он вставляет обычные ссылки на ячейки рабочего листа.

Зная это правило, мы легко получим «нормальные» ссылки на ячейки сводной таблицы. Для этого делаем так:

1. Открываем документ, как на рис. 1.

2. Становимся на ячейку « D3 ».

3. Вводим символ « = » (начинаем запись формулы).

4. Щелкаем левой кнопкой мыши на ячейке « A3 ». Excel добавит в текущую ячейку ссылку « =A3 », где записано название фирмы. В данном конкретном случае — это « ТОВ «Топаз» ».

5. Нажимаем « Enter » (завершаем ввод формулы).

6. Копируем формулу в ячейку « E3 ».

Смотрим на содержимое ячеек « D3 » и « E3 ». Как и следовало ожидать, там находятся обычные ссылки: « = A3 » и « =B3 ». Одна указывает на ячейку с названием фирмы, вторая — на объем реализации. Теперь с этими ссылками можно делать все что угодно — переносить на другой лист, использовать в расчетах и т. д.

И последнее. Работа с обычными ссылками незаменима, когда нужно построить график по данным сводного отчета (!) в программе Excel 2003. При создании такого графика Excel 2003 формирует его на отдельном листе, а это не всегда удобно. Чтобы отказаться от такой возможности и построить диаграмму на текущем листе, нужно создать рабочую область со ссылками на данные сводной таблицы. А затем по этим ссылкам сформировать диаграмму. Для таблицы на рис. 1 процедура выглядит так:

1. Открываем документ, переходим на ячейку « D3 ».

2. Вводим в нее формулу « =A3 ».

3. Копируем формулу в ячейки « D3:E8 ». В результате мы получим копию данных из сводной таблицы в виде формул.

4. Строим график по данным « D3:E8 ».

5. Чтобы скрыть «рабочую область», форматируем значения в блоке « D3:D8 » белым цветом или ставим график поверх ячеек « D3:D8 », чтобы закрыть им вспомогательную информацию (рис. 2).

На сегодня все. Удачной работы! Жду ваши вопросы, замечания и предложения на bk@id.factor.ua , nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum .

Наш сайт корисний для вас?

Підписатися на найактуальнішу розсилку для бухгалтера бюджетної установи

Умные таблицы в Excel

Постановка задачи

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

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

Выделяем любую ячейку в таблице и на вкладке Главная (Home) разворачиваем список Форматировать как таблицу (Format as table) :

В раскрывшемся списке стилей выбираем любой вариант заливки на наш вкус и цвет и в окне подтверждения выделенного диапазона жмем ОК и получаем на выходе примерно следующее:

В результате после такого преобразования диапазона в «умную» Таблицу (с большой буквы!) имеем следующие радости (кроме приятного дизайна):

  1. Созданная Таблицаполучает имяТаблица1,2,3 и т.д. которое, можно изменить на более адекватное на вкладке Конструктор (Design) . Это имя можно использовать в любых формулах, выпадающих списках и функциях, например в качестве источника данных для сводной таблицы или массива поиска для функции ВПР (VLOOKUP).
  2. Созданная один раз Таблицаавтоматически подстраивается в размерах при добавлении или удалении в нее данных. Если дописать к такой Таблице новые строки — она растянется ниже, если добавить новые столбцы — разойдется вширь. В правом нижнем углу Таблицы можно увидеть автоматически перемещающийся маркер границы и, при необходимости, скорректировать его положение мышью:

  • В шапке Таблицы автоматически включается Автофильтр (можно принудительно отключить на вкладке Данные (Data) ).
  • При добавлении новых строк в них автоматически копируются все формулы.
  • При создании нового столбца с формулой — она будет автоматически скопирована на весь столбец — не надо тянуть формулу черным крестом автозаполнения.
  • При прокрутке Таблицы вниз заголовки столбцов (A, B, C…) меняются на названия полей, т.е. уже можно не закреплять шапку диапазона как раньше (в Excel 2010 там еще и автофильтр):

    Включив флажок Показать итоговую строку (Total row) на вкладке Конструктор (Design) мы получаем автоматическую строку итогов в конце Таблицы с возможностью выбора функции (сумма, среднее, количество и т.д.) по каждому столбцу:

  • К данным в Таблице можно адресоваться, используя имена отдельных ее элементов. Например, для суммирования всех чисел в столбце НДС можно воспользоваться формулой =СУММ(Таблица1[НДС]) вместо =СУММ(F2:F200) и не думать уже про размеры таблицы, количество строк и корректность диапазонов выделения. Также возможно использовать еще следующие операторы (предполагается, что таблица имеет стандартное имя Таблица1):
    • =Таблица1[#Все] — ссылка на всю таблицу, включая заголовки столбцов, данные и строку итогов
    • =Таблица1[#Данные] — ссылка только на данные (без строки заголовка)
    • =Таблица1[#Заголовки] — ссылка только на первую строку таблицы с заголовками столбцов
    • =Таблица1[#Итоги] — ссылка на строку итогов (если она включена)
    • =Таблица1[#Эта строка] — ссылка на текущую строку, например формула =Таблица1[[#Эта строка];[НДС]] — будет ссылаться на значение НДС из текущей строки таблицы.

    (В англоязычной версии эти операторы будут звучать, соответственно, как #All, #Data, #Headers, #Totals и #This row).

    В Excel 2003 было что-то отдаленно похожее на такие «умные» таблицы — называлось Списком и создавалось через меню Данные — Список — Создать список (Data — List — Create list) . Но даже половины нынешнего функционала там не было и в помине. В более древних версиях Excel не было и этого.

    Ссылка на основную публикацию
    Adblock
    detector