Создание связанных таблиц в excel

Создание связанных таблиц в excel

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

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

В зависимости от техники исполнения связывание бывает “прямым“и через командуСПЕЦИАЛЬНАЯ ВСТАВКА.

1 Способ – "Прямое связывание ячеек"

Прямое связываниелистов используется непосредственно при вводе формулы в ячейку, когда в качестве одного из элементов формулы используется ссылка на ячейку другого листа. Например, если в ячейке таблицы В4 на рабочем Листе2 содержится формула, которая использует ссылку на ячейку А4 другого рабочего листа (например, Листа1) и оба листа загружены данными, то такое связывание листов называется “прямым”.

Термин “прямое” связываниеобозначает, чтопользователь сам непосредственно при вводе формулыуказываетимя листаиабсолютный адрес ячейки, разделенные восклицательным знаком "!".

Примеры формул: = C5*Лист1! A4

= Лист1! A1- Лист2! A1

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

2 Способ – Связывание ячеек через команду "Специальная вставка"

Связывание через команду СПЕЦИАЛЬНАЯ ВСТАВКАпроизводится, если какая либо ячейка таблицы на одном рабочем листе должна содержать значение ячейки из другого рабочего листа.

Чтобы отразить в ячейке С4 на листе Цена значение ячейки Н4 на исходном листеЗакупка, нужно поместить курсор на ячейку Н4 исходного листа и выполнить командуПравка–Копировать. На листеЦенапоставить курсор на ячейку С4, которую необходимо связать с исходной, и выполнить командуПравка–Специальная вставка–Вставить связь(см рис. 8). Тогда на листеЦенапоявится указание на ячейку исходного листа Закупка, например:= Закупка!$Н$4

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

Задание. Свяжите ячейки С4, С5, С6, С7, С8 в таблицеРасходы на закупкуна листеЦенас соответствующими ячейками на листеЗакупка, используя различные способы связывания ячеек (рис. 8).

Рис. 8 Связывание ячеек различных рабочих листов

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

Читайте также:  Как убрать контрастность на windows 10

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

Задания для самостоятельной работы.

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

на листе Цена в таблицеРасходы на закупкуячейки А4:А8 связаны с ячейками таблицыКоличество закупленной продукциина листеЗакупка;

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

ячейки С4:С8 связаны с ячейками Н4:Н8 на листе Закупка;

ячейки D4:D8 содержат формулы подсчета затраченных средств на приобретенный товар и ссылаются на ячейки собственной таблицы (например, формула в ячейкеD4 имеет вид =В4*С4, что означает умножение цены товара на его количество);

ячейка D9 является суммой ячеекD4:D8;

во второй таблице Расчет ценна этом же листе ячейки А14:А18 связаны аналогично п.1;

ячейки В14:В18 являются связанными с исходными ячейками текущего листа В4:В8;

ячейки С4:С8 являются исходными, т.к. содержат первоначальные сведения о наценке салона на закупленный товар;

ячейки D14:D18 содержат формулы расчета цены продажи товара и ссылаются на ячейки собственной таблицы (например, формула в ячейкеD14 имеет вид =В14*С14+В14, что означает умножение закупочной цены на установленный процент наценки, что дает сумму наценки, которую надо прибавить к закупочной цене);

После выполнения всех операций с этими таблицами произведите проверку их "работоспособности".

Изменитенаименование товара –Диванв ячейке А4 на листеЗакупкана другое – напримерСофа.

Изменитеколичество закупленного товараСофав июне (в ячейкеG4 на листеЗакупкавведите число 11).

Изменитецену закупки Софы в ячейке В4 на листеЦенана другую – 2500,00 р.

Изменитепроцент наценки Софы в ячейке С14 на листеЦена с 50% на 32%.

Проверьте, произошли изменения в связанных таблицах или нет?

Обратите внимание, на какие ячейки различных таблиц повлияли внесенные изменения.

Внимание! При связывании ячеек через СПЕЦИАЛЬНУЮ ВСТАВКУ. копирование на соседние ячейки становится проблематичным из-за абсолютной адресации ячеек.

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

Указание. В таблицах по расчету выручки и дохода за 1 квартал используется формула суммирования исходных ячеек только 1 квартала.

Задание 2. Создайте на листах Выручка и Доход таблицы по расчету за 2 квартал. Свяжите эти таблицы с соответствующими исходными данными.

Указание. В таблицах по расчету выручки и дохода за 2 квартал используйте исходные ячейки только 2 квартала.

Задание 3. Постройте круговую диаграмму на листе Доход и проанализируйте распределение дохода по видам продукции.

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

Читайте также:  Что такое android wear

Указание. В таблицах по расчету выручки и дохода за 2 квартал используйте исходные ячейки только 2 квартала.

Связанная таблица — это набор данных, которыми можно управлять как единым целым.

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

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

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

Каждой связанной таблице дается уникальное имя. По умолчанию — "Таблица_номер". Изменить название таблицы можно на панели "Свойства".

На панели "Стили таблиц" можно, при необходимости, изменить или настроить стиль связанной таблицы.

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

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

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

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

В связанную таблицу можно добавлять/удалять строки и столбцы.

Это можно делать несколькими способами.

1. Воспользоваться кнопкой "Изменить размер таблицы" на панели "Свойства".

2. Установите курсор в ячейке связанной таблицы, рядом с которой надо добавить новый столбец (строку) и на панели "Ячейки" ленты "Главная" воспользуйтесь кнопкой "Вставить".

3. Не забывайте также о контекстном меню.

В начало страницы

В начало страницы

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

Как связать две таблицы одной формулой для выборки ВПР по условию

Ниже на рисунке представлена таблица для вычисления налоговой суммы. Пользователь имеет возможность определять семейное положение сотрудника (женат или Неженат). Если пользователь выберет условие «Неженат», выборка должна выполнятся по таблице «Неженатые сотрудники». Если будет выбран критерий «Женат» выборка будет произведена по таблице «Женатые сотрудники». Формула для расчета налогов при условии женат или Неженат сотрудник фирмы:

Читайте также:  Программа для замера разгона автомобиля android

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

  1. Женат – для таблицы «Женатые сотрудники».
  2. Неженат – для таблицы «Неженатые сотрудники».



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

  1. Выделите диапазон ячеек A3:D10.
  2. Выберите инструмент: «ФОРМУЛЫ»-«Определенные имена»-«Присвоить имя». Появится окно «Создание имени» как показано на рисунке:
  3. В поле «Имя:» введите значение – Женат. И нажмите ОК.
  4. Выделите диапазон ячеек из второй таблицы F3:I10.
  5. Снова выберите инструмент «Присвоить имя» на вкладке «ФОРМУЛЫ» и заполните поле «Имя:» значением – Неженат, как на рисунке:
  6. Нажмите Ок.

Для точности и удобства ввода входных значений в ячейке… используется выпадающий список создан инструментом: «ДАННЫЕ»-«Работа с данными»-«Проверка данных»-«Тип данных:»-«Список».

Выпадающий список состоит только из двух значений: «Женат» «Неженат». Точно такие же как названия имен диапазонов ячеек, созданных ранее. Значение ячейки E12 будет использовано для переключения между таблицами при поиске по условию. Поэтому значения и имена диапазонов должны быть идентичны.

В основе данной формулы лежит функция ВПР. Ее второй аргумент где указывается исходная таблица содержит функцию ДВССЫЛ. Данная функция имеет первый аргумент «Ссылка на ячейку», который преобразует входящий текст в ссылку на ячейку или диапазон. На самом первом рисунке ячейка E12 содержит значение «Неженат». Функция ДВССЫЛ пытается преобразовать этот текст в ссылку на ячейку или в имя диапазона. Если текст не преобразовывается в ссылку на ячейку (как в данном примере), тогда функция ДВССЫЛ проверяет нет ли в данной рабочей книге имен диапазонов ячеек с таким же названием. Если небыли бы созданы такие имена диапазонов, тогда функция вернула бы ошибку с кодом #ССЫЛКА!

В синтаксисе функции ДВССЫЛ имеется второй необязательный для заполнения аргумент – называется «A1». Значение ИСТИНА в данном аргументе значит, что ссылка на ячейку записана в формате A1, а значение ЛОЖЬ – формате R1C1. В случае названых имен диапазонов ячеек функция ДВССЫЛ вернет правильный результат в независимости от того, что указано во втором опциональном ее аргументе «A1»: ИСТИНА или ЛОЖЬ.

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

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