Первое и самое главное: связывание таблиц по общим признакам — не типичная функция для MS Excel. Для таких задач есть специальные программы — так называемые базы данных. Хотя правильнее их называть «системы управления базами данных», или СУБД. К ним относятся, например, MS Access, MS SQL Server, Oracle и многие другие. В этих системах объединение таблиц по общим ключевым полям — едва ли не самая распространенная операция. И для ее решения там есть мощные и удобные инструменты. Они позволяют организовать самые разнообразные типы связей между таблицами, контролировать целостность данных, выполнять каскадное удаление записей и т. п. Однако для полноценного использования СУБД нужна определенная подготовка, знание языка запросов и т. п. Поэтому в практике бухгалтера СУБД в чистом виде применяют редко, и всю обработку данных делают обычно в программе Excel. Что же касается MS Excel, то здесь возможности связывания таблиц ограничены. В основном эта связь заключается в написании формул, которые ссылаются на разные листы или рабочие книги. Вы можете создать печатный отчет или сводную таблицу, которая оперирует несколькими базами данных. Но специальных инструментов для полноценной организации связей между наборами данных в Excel нет, да и быть не должно.
Однако не все так плохо… Дело в том, что в реальной работе все варианты связывания таблиц бухгалтеру обычно не нужны. Да и без каскадного удаления записей он тоже как-то обойдется. А вот связать одну большую таблицу со справочником — задача вполне актуальная и практически применимая. И, что самое главное(!), такая функция вполне реализуема в MS Excel. Причем единственное, что для этого потребуется, — это пара несложных трюков и система гиперссылок.
Теперь посмотрим, как это выглядит практически. Начнем, конечно же, с исходных данных.
Чем мы располагаем
В качестве примера я воспользуюсь двумя таблицами, форма которых показана на рис. 1 и 2. Первая таблица (она расположена на листе « Заказы ») состоит из четырех полей: « Заказ », « Дата », « Клиент », « Сумма » и пока пустое поле « Коммент. ». Это упрощенный вариант базы данных о заказах. В поле « Коммент. » мы в конечном итоге и поставим ссылку на элемент справочника. Понятно, что в базе « Заказы » один контрагент может фигурировать много раз, — это зависит от количества заказов, сделанных этим клиентом.
Вторая таблица называется « Клиенты ». В ней я оставил всего пять колонок: « Название » (наименование предприятия), далее идут фамилия директора, юридический адрес, телефон и e-mail.
Важно! В справочнике « Клиенты » одна строка содержит данные об одном предприятии. Повторы здесь недопустимы, каждый клиент присутствует в базе только один раз.
Теперь вернемся непосредственно к самой задаче.
Что нужно сделать
Итак, мы собираемся организовать связь между таблицей « Заказы » и « Клиенты ». Для этого в поле « Коммент. » таблицы « Заказы » мы поставим гиперссылку, которая будет указывать на определенную строку таблицы « Клиенты ». Алгоритм работы гиперссылки будет таким. Вначале она должна проанализировать содержимое поля « НаимПредпр » в текущей строке таблицы « Заказы ». Иными словами, запомнить название предприятия , которое оформило конкретный заказ. Затем ссылка должна выполнить переход на лист « Клиенты ». На этом листе — найти строку, которая описывает параметры предприятия, сделавшего заказ. И, наконец, — установить указатель текущей ячейки на соответствующую строку в справочнике « Клиенты ». Так это выглядит в общих чертах.
Кстати, в терминологии баз данных такая связь называется « один ко многим » (если смотреть со стороны таблицы « Клиенты »). То есть один элемент справочника о клиентах ссылается на несколько записей в базе заказов. В MS Excel для создания такой связи специальных инструментов нет. Мы должны построить их сами. Для этого, исходя из алгоритма, нам понадобятся: встроенная функция для создания гиперссылки (такая функция называется « ГИПЕРССЫЛКА() ») и функция для поиска данных в таблице. Я предлагаю для поиска использовать функцию « ПОИСКПОЗ() ». Но это не все. Гиперссылке нужно указать точный адрес для перехода: имя файла, название рабочего листа и ячейку, которую нужно сделать активной. Для получения имени файла, листа и ячейки мы воспользуемся функцией « ЯЧЕЙКА() ». Кроме того, для формирования строки с адресом перехода нам придется обратиться к встроенным функциям работы с текстом: « ПСТР() » и « ПОИСК() ». Вот собственно и все основные инструменты. Можно переходить к практической реализации.
Связываем две таблицы
Вначале выполним несколько предварительных действий. Начнем с функции « ГИПЕРССЫЛКА() », которая будет ключевой при организации связей между таблицами. У нее два параметра. Первый — это адрес, т. е. имя файла и рабочего листа, на который указывает ссылка. Второй параметр — адрес конкретной ячейки на этом листе, куда будет указывать гиперссылка. У нас гиперссылок будет много. И каждый раз для их создания придется указывать в параметрах функции имя файла и название листа. Это долго и неудобно. Поэтому лучше один раз создать переменную с названием листа и файла, а затем указывать ее переменную в функции « ГИПЕРССЫЛКА() » по мере необходимости. Применительно к MS Excel в качестве такой переменной удобно использовать именованный диапазон ячеек . С этого мы и начнем.
Формируем переменную с названием листа
Итак, нам нужно создать переменную, чтобы сохранить в ней имя файла и название рабочего листа для будущих гиперссылок. Мы решили, что такой переменной будет именованный диапазон. Чтобы создать его в MS Excel 2010, делаем так.
1. Вызываем Excel, загружаем документ и переходим в меню « Формулы ». Лента примет вид, как на рис. 3.
2. В группе « Определенные имена » щелкаем на иконке « Диспетчер имен ». Откроется окно, изображенное на рис. 4.
3. В этом окне нажимаем кнопку « Создать ». Откроется окно « Создание имени », изображенное на рис. 5.
4. В этом окне в поле « Имя: » вводим текст « Мой_Лист ». В поле « Диапазон: » печатаем формулу « =ПСТР(ЯЧЕЙКА(" имяфайла " ;Клиенты!$A$1);ПОИСК(" [ " ;ЯЧЕЙКА(" имяфайла " ;Клиенты!$A$1));256)& " ! " » (рис. 3).
5. В окне « Создание имени » нажимаем « ОК ».
6. В окне « Диспетчер имен » нажимаем « ОК ».
Теперь разберемся, что же мы сделали на самом деле? В рабочей книге появился новый именованный диапазон « Мой_Лист ».
Важно! В нашем случае название именованного диапазона должно быть БЕЗ пробелов. В противном случае формулы адресации будут работать неправильно.
Как и любой именованный диапазон, он указывает на ячейку или группу ячеек рабочей книги Excel. Но в нашем случае адрес этой группы не постоянный, а динамический . Его формирует формула, которую мы ввели в поле « Диапазон: ». Вкратце о работе этой формулы. Начнем изнутри — с функции « ЯЧЕЙКА() ».
Выражение « ЯЧЕЙКА(" имяфайла " ;Клиенты! $A$1) » обращается к ячейке « A1 » на листе « Клиенты » и возвращает для этой ячейки ее полный адрес , т. е. путь к файлу, имя файла и название листа, где эта ячейка расположена. Например, документ с таблицами у меня называется « ДинСсылкиExcel_.xls ». Хранится он на диске « D: » в папке « !Фактор ». Тогда результат работы формулы будет такой: « D:!Фактор[ДинСсылкиExcel_.xls]Клиенты ».
Из этой строки нам нужно взять только имена файла и лист а рабочей книги — буква диска и название папки при создании гиперссылки не понадобятся. Для решения этой задачи мы воспользуемся стандартными функциями Excel для работы с текстом.
Вырезать часть строки можно функцией « ПСТР() ». Но ей нужно указать: исходный текст (у нас это полный путь к ячейке), начальную позицию и количество знаков , которое нужно вырезать из исходной строки.
Начальную позицию определить просто. Для этого с помощью функции « ПОИСК() » мы находим первое вхождение квадратной открывающей скобки (« [ ») в текст, где хранится путь к ячейке. Фрагмент формулы, который выполняет эту операцию, выглядит так: « ПОИСК(" [ " ;ЯЧЕЙКА(" имяфайла " ; Клиенты!$A$1)) ». Для строки « D:!Фактор [ДинСсылкиExcel_.xls]Клиенты » эта формула вернет « 12 ». Количество знаков, которые нужно вырезать из исходного текста, я выбрал максимальное — « 256 ».
Остается подставить полученные значения в функцию « ПСТР() ». Первым ее параметром идет текст с адресом к ячейке. Второй параметр — номер начальной позиции для вырезания части строки. Последний параметр — максимально допустимое количество символов в строке. Формула для этой операции будет такой: « =ПСТР(ЯЧЕЙКА (" имяфайла " ;Клиенты!$A$1);ПОИСК
(" [ " ;ЯЧЕЙКА(" имяфайла " ;Клиенты!$A$1));256) ». В нашем примере такая формула вернет результат в виде « [ДинСсылкиExcel_.xls]Клиенты ». По сути — это ссылка на лист « Клиенты » рабочей книги « ДинСсылкиExcel_.xls ». Дальше может идти адрес ячейки. Но пока не хватает одного элемента — между именем листа и адресом ячейки должен стоять восклицательный знак (« ! »). Этот символ мы можем присоединить к формуле при помощи операции « & », и в окончательной редакции выражение будет выглядеть так: « =ПСТР(ЯЧЕЙКА(" имяфайла " ;Клиенты!$A$1);ПОИСК(" [ " ;ЯЧЕЙКА(" имяфайла " ; Клиенты!$A$1)); 256)& " ! " ». А результат ее работы будет такой: « ».
Совет Аккуратно скопируйте фрагменты формулы в отдельные ячейки рабочего листа, и тогда работа каждой ее части будет у вас как на ладони.
Кстати, работу формулы внутри именованного диапазона легко проверить. Введите в любую свободную ячейку листа выражение « =Мой_Лист » и нажмите клавишу « Enter ». В ячейке должен появиться результат: « [ДинСсылкиExcel_.xls]Клиенты! ».
Важно! Обратите внимание, что для правильной работы формулы имена листов не должны содержать пробелов.
1. Переходим на лист « Заказы », становимся на ячейку « E2 ».
2. Вводим формулу « =ГИПЕРССЫЛКА(Мой_ Лист&АДРЕС(ПОИСКПОЗ(C2;Клиенты!$A:$A;0); 1); " > " ) ».
3. Копируем эту формулу на всю высоту таблицы. Результат нашей работы показан на рис. 6.
Проверяем, что у нас получилось. В таблице « Заказы » щелкаем левой кнопкой, например, на ячейке « E6 ». В этой строке расположены сведения о заказе с номером « 5 » от фирмы « ЧП " Коло " ». После щелчка Excel переключится на лист « Клиенты », а указатель активной ячейки станет на адрес « A6 ». Именно в этой позиции справочника записана информация о « ЧП " Коло " » (рис. 7).
Рассмотрим вкратце алгоритм работы формулы. Выражение « =ПОИСКПОЗ(C2;Клиенты! $A:$A;0) » находит ячейку в колонке « А » на листе « Клиенты », в которой встречается название контрагента из ячейки « С2 » листа « Заказы ». Последний параметр функции « ПОИСКПОЗ() » равен « 0 ». Это означает, что она будет искать значение по принципу точного совпадения. Вернемся к нашему примеру. Предположим, что мы работаем со строкой « 6 » таблицы « Заказы » (рис. 6). В этой строке находится заказ с номером « 5 » от фирмы « ЧП " Коло " ». После копирования гиперссылки из ячейки « E2 » вниз по колонке « E » в ячейке « E6 » формула с функцией поиска получится такой: « ПОИСКПОЗ(C6;Клиенты!$A:$A;0) ». А результат работы этого выражения будет равен « 6 ». Это означает, что в таблице « Клиенты » описание фирмы « ЧП «Коло» » расположено в шестой строке рабочего листа.
Идем дальше. Результат работы этого выражения мы подставим в качестве первого параметра функции адрес. Вторым параметром укажем « 1 ». Тогда выражение « АДРЕС(ПОИСКПОЗ (C2;Клиенты!$A:$A;0);1) » (вторая строка таблицы « Заказы ») по сути означает « АДРЕС(2;1) ». Эта функция вернет в виде текста адрес ячейки, которая находится во второй строке и первой колонке рабочего листа. Для заказа « 1 » (вторая строка) это будет адрес « $A$2 ». Для заказа в шестой строке таблицы (фирма « ЧП " Коло " ») выражение вернет строку « $A$6 » и т. д.
С адресацией ячейки понятно. Теперь нужно использовать полученный результат в функции « ГИПЕРССЫЛКА() ».
Здесь ситуация такова. Напомню, что у функции « ГИПЕРССЫЛКА() » два параметра. Первый — это адрес, куда указывает гиперссылка. Он включает название файла, листа и адрес ячейки для перехода. Имена файла и листа у нас уже есть, эта информация хранится в переменной « Мой_Лист ». Адрес ячейки для ссылки на контрагента по конкретному заказу мы получили. Остается объединить эти две части операцией « & ». И тогда окончательное выражение для адреса перехода получится таким: « Мой_Лист&АДРЕС(ПОИСКПОЗ(C2; Клиенты!$A:$A;0);1) ».
Смотрим, что у нас получилось. Для ячейки « С2 » такая формула вернет результат « [ДинСсылкиExcel_.xls]Клиенты!$A$2 » — т. е. ссылку на ячейку « A2 » в справочнике « Клиенты ». Все верно: для заказа с номером « 1 » ссылка указывает на контрагента « ТОВ " Смит " ». Если обратиться к ячейке « С6 » (заказ с номером « 5 » от « ЧП " Коло " »), то в этой строке таблицы « Заказы » выражение для адреса перехода будет выглядеть так: « Мой_Лист&АДРЕС(ПОИСКПОЗ(C6;Клиенты!$A:$A;0);1) ». А результат формулы будет такой: « [ДинСсылкиExcel_.xls]Клиенты!$A$6 ».
Переходим ко второму параметру функции « ГИПЕРССЫЛКА() ». Здесь должен находиться текст, который Excel покажет на месте гиперссылки. Иными словами, это название самой гиперссылки. Я выбрал в качестве такого названия символ « > » (знак « больше »). И тогда окончательная формула для создания гиперссылки будет такой: « =ГИПЕРССЫЛКА(Мой_Лист&АДРЕС(ПОИСКПОЗ(C2; Клиенты!$A:$A; 0);1); " > " ) ».
Вот собственно и все относительно создания связей между таблицами Excel. С задачей мы справились, все ссылки работают правильно. Единственное, хотелось бы добавить пару слов по оформлению таблицы. Сейчас в колонке «
Иногда данные, которые нужно найти, могут находится на разных таблицах. Часто приходится делать выборку данных по нескольким таблицам Excel. Пользователь должен иметь возможность определять из какой таблицы делать выборку по критериям условий для того или иного типа данных. Для этого можно создать специальную формулу.
Как связать две таблицы одной формулой для выборки ВПР по условию
Ниже на рисунке представлена таблица для вычисления налоговой суммы. Пользователь имеет возможность определять семейное положение сотрудника (женат или Неженат). Если пользователь выберет условие «Неженат», выборка должна выполнятся по таблице «Неженатые сотрудники». Если будет выбран критерий «Женат» выборка будет произведена по таблице «Женатые сотрудники». Формула для расчета налогов при условии женат или Неженат сотрудник фирмы:
Чтобы создать переключатель между таблицами можно использовать имена диапазонов ячеек и функцию ДВССЫЛ. После чего нужно составить формулу. Необходимо сначала создать два именных диапазона:
- Женат – для таблицы «Женатые сотрудники».
- Неженат – для таблицы «Неженатые сотрудники».
Чтобы присвоить отдельные имена для каждого из диапазонов этих двух таблиц сделайте следующее:
Для точности и удобства ввода входных значений в ячейке… используется выпадающий список создан инструментом: «ДАННЫЕ»-«Работа с данными»-«Проверка данных»-«Тип данных:»-«Список».
Выпадающий список состоит только из двух значений: «Женат» «Неженат». Точно такие же как названия имен диапазонов ячеек, созданных ранее. Значение ячейки E12 будет использовано для переключения между таблицами при поиске по условию. Поэтому значения и имена диапазонов должны быть идентичны.
В основе данной формулы лежит функция ВПР. Ее второй аргумент где указывается исходная таблица содержит функцию ДВССЫЛ. Данная функция имеет первый аргумент «Ссылка на ячейку», который преобразует входящий текст в ссылку на ячейку или диапазон. На самом первом рисунке ячейка E12 содержит значение «Неженат». Функция ДВССЫЛ пытается преобразовать этот текст в ссылку на ячейку или в имя диапазона. Если текст не преобразовывается в ссылку на ячейку (как в данном примере), тогда функция ДВССЫЛ проверяет нет ли в данной рабочей книге имен диапазонов ячеек с таким же названием. Если небыли бы созданы такие имена диапазонов, тогда функция вернула бы ошибку с кодом #ССЫЛКА!
В синтаксисе функции ДВССЫЛ имеется второй необязательный для заполнения аргумент – называется «A1». Значение ИСТИНА в данном аргументе значит, что ссылка на ячейку записана в формате A1, а значение ЛОЖЬ – формате R1C1. В случае названых имен диапазонов ячеек функция ДВССЫЛ вернет правильный результат в независимости от того, что указано во втором опциональном ее аргументе «A1»: ИСТИНА или ЛОЖЬ.
Функция ДВССЫЛ может также возвращать внешние ссылки на другие листы и даже другие рабочие книги Excel. Но при условии, что рабочая книга, на которую ссылается функция будет открыта. Иначе будет возвращена ошибка с кодом #ССЫЛКА!
Связанная таблица - это набор данных, которыми можно управлять как единым целым.
Для создания связанной таблицы предназначена кнопка "Форматировать как таблицу" на панели "Стили" ленты "Главная" .
Выберите нужный стиль будущей таблицы и задайте диапазон ячеек, на основе которого будет создана связанная таблица.
После настройки всех параметров мы получим связанную таблицу. Рядом с заголовками столбцов появятся кнопки со стрелочками (по аналогии с фильтрацией), а в окне программы появится контекстный инструмент "Работа с таблицами" , содержащий ленту "Конструктор" .
Каждой связанной таблице дается уникальное имя. По умолчанию - "Таблица_номер". Изменить название таблицы можно на панели "Свойства" .
На панели "Стили таблиц" можно, при необходимости, изменить или настроить стиль связанной таблицы.
Связанную таблицу можно обратно превратить в обычный диапазон ячеек. Для этого предназначена кнопка "Преобразовать в диапазон" на панели "Сервис" контекстной ленты "Конструктор" .
Как уже упоминалось ранее, рядом с заголовками столбцов расположены кнопки сортировки и фильтрации, при помощи которых можно сортировать данные и применять фильтры.
В связанной таблице можно подсчитывать итоговые значения в столбцах. Для этого надо установить флажок "Строка итогов" на панели "Параметры стилей таблиц" .
При помощи выпадающего списка можно выбрать нужное значение итоговой функции.
В связанную таблицу можно добавлять/удалять строки и столбцы.
Это можно делать несколькими способами.
1. Воспользоваться кнопкой "Изменить размер таблицы" на панели "Свойства" .
2. Установите курсор в ячейке связанной таблицы, рядом с которой надо добавить новый столбец (строку) и на панели "Ячейки" ленты "Главная" воспользуйтесь кнопкой "Вставить" .
По мере того как вы используете и создаете больше рабочих документов Excel, у вас может возникнуть необходимость связать их между собой. Может быть вы захотите написать формулу, которая использует данные с разных Листов. Или может даже вы напишете формулу, которая будет использовать данные из разных Книг (стандартное название рабочего документа Excel - workbook (анг.))
Я понял, что если я хочу, чтобы мои файлы были понятными и аккуратными, лучше разделять большие Листы с данными от формул, которые их обрабатывают. Я часто использую одну Книгу или Лист, который обрабатывает все что мне нужно.
В этом уроке, вы узнаете как добавлять ссылки на данные в Excel. Сначала, мы научимся связывать данные из разных Листов в пределах одной Книги. Затем, мы перейдем к связыванию нескольких Книг Excel, чтобы импортировать и синхронизировать данные между файлами.
Как Можно Быстро Организовать Ссылку Между Данными в Книгах Excel (Смотри и Учись)
Я покажу вам два примера, как можно связать данные из ваших электронных таблиц. Вы узнаете как извлекать данные из другой таблицы в Excel и как организовать связь между двумя Книгами. Мы также рассмотрим основные примеры написания формул, использующих данные из различных Листов в пределах одного рабочего документа.
Давайте посмотрим иллюстрированное руководство по связыванию Листов и Книг в Excel.
Основы: Как Добавить Ссылку между Листами в Excel
Давайте начнем с того, что научимся создавать формулы, которые используют данные с других Листов. Вы возможно уже знаете, что рабочий документ (Книга) Excel, может содержать несколько рабочих страниц (Листов). Каждый лист имеет свою вкладку, и вы можете переключаться между ними щелкая по вкладкам в нижней части документа Excel.
Сложные документы могут содержать большое количество листов. И в какое-то время, вам придется написать формулу которая будет использовать данные из разных листов.
Может вы будете использовать один лист в документе для всех ваших формул, которые обрабатывают данные, а данные будете распределять по отдельным листам.
В моем документе три вкладки с Листами (Sheet). Я собираюсь написать формулу, которая будет работать с данными из каждого листа.Давайте разберемся как написать многостраничную формулу, которая работает с данными из разных Листов в пределах одного рабочего документа.
1. Создайте в Excel новую Формулу
Многие формулы в Excel начинаются со знака равно (= ). Дважды щелкните или начните печатать в ячейке, и вы начнете создавать формулу, в которую вы хотите вставить ссылку. Например, я собираюсь написать формулу, которая будет суммировать значения из разных ячеек.
Я открываю запись знаком = , и затем щелкаю по первой ячейке на текущем листе, чтобы создать первую часть формулы. Затем, я напечатаю знак + , чтобы добавить значение из второй ячейки в этой формуле.
Начните записывать формулу в ячейке и щелкните по первой ячейке, чтобы бы включить ее в формулу, но при этом, пока не завершайте ввод формулы.Пока не завершайте ввод формулы и не жмите Enter! Вам нужно оставить формулу открытой перед тем как вы переключитесь на другой Лист.
2. Переключитесь между Листами в Excel
Пока ваша формула не закончена, щелкните на вкладке другого листа в нижней части программы. Очень важно, чтобы вы не закрывали формулу, перед тем как вы щелкните по другой ячейке, которую вы хотите вставить в формулу.
Перейдите на другой Лист ExcelПосле того, как вы переключитесь на другой лист, щелкните по ячейке которую вы хотите добавить в формулу. Как вы можете видеть на картинке ниже, Excel автоматически запишет часть формулы, которая будет обращаться к ячейке на другом Листе.
Заметьте на картинке ниже, что для ссылки на ячейку на другом Листе, Excel добавил надпись "Sheet2!B3", которая ссылается на ячейку В3 на листе с названием Sheet2 . Вы можете написать это вручную, но когда в щелкаете по ячейке, Excel делает эту надпись автоматически за вас.
Excel автоматически записывает за вас часть формул, чтобы добавить ссылку на ячейку с другого Листа.3. Закройте Excel формулу
Теперь, вы можете нажать enter , чтобы завершить создание мульти-страничной формулы. Когда вы это делаете, Excel вернется туда, где вы начали создавать формулу и покажет вам результат.
Вы можете продолжать набирать формулу, включая в нее ячейки с других листов и другие ячейки на этом же листе. Продолжайте комбинировать ссылки со всего рабочего документа, на все данные, которые вам нужны.
Повышаем Уровень Сложности: Как Создать Связь Между Несколькими Книгами Excel
Давайте узнаем, как извлечь данные из другого рабочего документа. С помощью этого навыка, вы сможете писать формул, которые связывают данные из разных рабочих документов Excel.
Для этого раздела урока, вам понадобится два рабочих документа, которые вы можете к этому уроку. Откройте их оба в Excel, и следуйте дальнейшим указаниям.
1. Откройте Две Рабочие Книги
Давайте начнем создавать формулу, которая будет использовать данные из двух различных документов.
Самый простой способ сделать это, это открыть два документа Excel, одновременно и расположить их рядом друг с другом. Я использую для этого функцию Windows Snap, чтобы каждое окно занимало пол экрана. Вам нужно иметь две книги перед собой, чтобы писать формулу, которая будет взаимодействовать с двумя Книгами.
На рисунке ниже, я открыл два рабочих документа один рядом с другим, для которых я буду создавать формулы. В моем примере, я веду бизнес, и покупаю различные продукты, и продаю их в разные страны. Итак, я использую разные рабочие документы, чтобы отслеживать покупки/продажи и данные по стоимости.
В этом примере, я использую разные рабочие документы, чтобы отслеживать покупки/продажи и данные по стоимости.2. Начните Создавать Формулу в Excel
Цена за которую я покупаю товары, может измениться, а так же может измениться соотношение курсов к валюте в которой я получаю выплаты. Поэтому мне нужно иметь список в котором отслеживаются курсы валют и умножать их на стоимость моих закупок. Это подходящая возможность, чтобы использовать информацию из двух файлов и записать формулу, которая будет использовать данные обоих файлов.
Давайте рассмотрим число баррелей нефти которую я покупаю каждый месяц и цену за баррель. Сначала в ячейке из колонки Cost (ячейка C3 ), я начинаю создавать формулу, щелкнув в ней и нажав знак равно (= ), а затем щелкаю по ячейке В3 , чтобы взять значение для количества. Теперь, я жму знак * , чтобы уможножить количество на курс.
Итак, ваша формула, пока должна выглядеть так:
Не закрывайте пока вашу формулу. Убедитесь, что она осталась открытой перед тем как перейти к следующему шагу; нам нужно указать Excel данные по цене, на которую будет умножаться количество.
3. Переключитесь на Другой Рабочий Документ Excel
Пришло время переключиться на другой рабочий документ, вот почему так важно иметь данные из двух документов между собой, когда вы работаете с двумя Книгами.
Пока ваша формула открыта, щелкните по вкладке другой книги. Затем, щелкните по ячейке в вашей второй Книге, чтоб связать данные из двух файлов Excel.
B3*Sheet1!$B$2
После того как вы нажмете Enter, Excel автоматически рассчитает окончательную стоимость умножив количество в первой книге на цену из второй книги.
Потренируйте свои навыки работы в Excel, умножая количество товара или объем на соответствующую ему цену из документа "Prices ".
Главное, расположить книги одну рядом с другой, и создавать формулу переключаясь между ними.
При этом, ничего не мешает вам использовать больше, чем два рабочих документа. Вы можете открыть много рабочих документов, на которые вы хотите ссылаться, и создавать формулы, связывающие данные между множеством листов.
Как Обновлять Данные При Работе с Несколькими Книгами
Когда вы используете формулы, которые ссылаются на разные книги Excel, вам нужно позаботится о том, как будут обновляться данные.
Итак, что же произойдет, если данные в Книге, на которую вы ссылаетесь, поменяются? Будут ли выполнены автоматические изменения в вашей Книге, или вам нужно обновлять ваши файлы, чтобы извлечь последние изменения и импортировать их?
Ответ такой, "зависит", и в основном зависит от того, открыты ли два документа одновременно.
Пример 1: Открыты Обе Книги Excel
Давайте проверим пример используя те же книги, что мы использовали раньше. Обе книги открыты. Давайте посмотрим, что произойдет, если мы изменим цену нефти за баррель с 45$ на 75$:
На сриншоте ниже, вы можете видеть, что когда мы изменили цену на нефть, результаты в другом документе автоматически поменялись.
Важно помнить: если оба рабочих документа открыты одновременно, изменения будут внесены автоматически в реальном времени. Когда вы меняете переменную, то информация в другом документе будет автоматически изменена или пересчитана, на основании новых данных.
Пример 2: Если Один Рабочий Документ Закрыт
А что если вы открыли только один рабочий документ? Например, каждое утро мы обновляем цены на наши товары и валюты, а вечером мы рассматриваем влияние изменений на наши покупки и продажи.
В следующий раз, когда вы откроете документ, который ссылается на другие таблицы, вы должны увидить сообщение подобное тому, что приведено ниже. Вы можете щелкнуть по кнопке Обновить (Update), чтобы извлечь полседние данные из документа, на который ссылается формула.
Щелкните по кнопке Обновить в выпадающем окне, когда откроете документ, чтобы извлечь полседние данные из друго файла.Вы так же можете видеть меню, где можно нажать на копку Включить содержимое (Enable Content), чтобы автоматически обновлять данные между файлами Excel.
Подводим итоги и продолжаем обучаться работе в Excell
Написание формул которые берут данные из разных Листов и Книг, важный навык, при работе в Excel. Тем более, что это совсем не сложно, если вы знаете как это делать.
Посмотрите эти дополнительные уроки, чтобы узнать больше о том как работать с программой Excel и как обрабатывать в ней данные. Эти уроки, хорошо подойдут для того, чтобы продолжить обучение.
Дайте мне знать в комментариях, если у вас остались какие-то вопросы, по поводу того как организовать связь между различными рабочими документами Excel.
Если нам нужно, чтобы автоматически данные из одной таблице Excel переносились в другую
, нужно связать эти таблицы. Есть несколько способов, как связать данные двух таблиц в Excel. Один способ -
с помощью формулы, смотрите в статье «Как связать таблицы в Excel ».
Здесь рассмотрим, как связать две таблицы в разных книгах Excel
, с помощью установки связи между таблицами. Когда все настроим, то достаточно открыть одну таблицу, чтобы данные перенеслись в эту таблицу из другой таблицы. Не нужно открывать все книги, чтобы связь работала. Она будет работать с одной открытой книгой.
Итак, у нас есть две книги Excel – Книга1 и Книга2. В Книге1 есть такая таблица, уже заполненная. Таблица1.
Внимание!
Если в таблице есть объединенные ячейки, то в них связь не будет работать или будет сбиваться.
В Книге2 делаем такую же таблицу, но не заполняем ее.
Переходим в Таблицу2. Выделяем ячейку А2. Нажимаем на закладке «Главная» стрелочку у кнопки «Вставить» и нажимаем на кнопку «Вставить связь».
Внимание!
Если нажмем клавишу «Enter», то связь может не установиться. Поэтому, когда установим связь, просто нажать на пустую ячейку. А пульсирующую рамку у ячейки А1 в Таблице1 убрать клавишей «Esc».
Когда установим связь в ячейке, в строке формул будет написан путь.
В ячейке А2 Таблицы2 появилось название столбца, как в Таблице1.
Теперь, чтобы скопировать связь по всему столбцу, в адресе пути в ячейке А1 уберем знак $ - заменим ссылку абсолютную на относительную. Подробнее о ссылках смотрите в статье «Относительные и абсолютные ссылки в Excel».
Копируем формулу со связью из ячейки А1 вниз по столбцу. Так же настраиваем и, затем, копируем, связь в других ячейках. Получилась такая таблица2.
О других функциях специальной вставки смотрите в статье "Специальная вставка Excel".
Как связать таблицы на разных компьютерах, смотрите в статье "Общий доступ к файлу Excel ".
В Excel можно настроить автоматический перенос данных из таблицы Excel в Word и наоборот. Смотрите в статье "