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

Автозаполнение ячеек в Excel из другой таблицы данных

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

Автозаполнение ячеек данными в Excel

Для наглядности примера схематически отобразим базу регистрационных данных:

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

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

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

Как сделать автозаполнение ячеек в Excel:

1. На листе «Регистр» введите в ячейку A2 любой регистрационный номер из столбца E на листе «База данных».
2. Теперь в ячейку B2 на листе «Регистр» введите формулу автозаполнения ячеек в Excel:
3. Скопируйте эту формулу во все остальные ячейки второй строки для столбцов C, D, E на листе «Регистр».

В результате таблица автоматически заполнилась соответствующими значениями ячеек.

Принцип действия формулы для автозаполнения ячеек

Главную роль в данной формуле играет функция ИНДЕКС. Ее первый аргумент определяет исходную таблицу, находящуюся в базе данных автомобилей. Второй аргумент – это номер строки, который вычисляется с помощью функции ПОИСПОЗ. Данная функция выполняет поиск в диапазоне E2:E9 (в данном случаи по вертикали) с целью определить позицию (в данном случаи номер строки) в таблице на листе «База данных» для ячейки, которая содержит тоже значение, что введено на листе «Регистр» в A2.

Третий аргумент для функции ИНДЕКС – номер столбца. Он так же вычисляется формулой ПОИСКПОЗ с уже другими ее аргументами. Теперь функция ПОИСКПОЗ должна возвращать номер столбца таблицы с листа «База данных», который содержит название заголовка, соответствующего исходному заголовку столбца листа «Регистр». Он указывается ссылкой в первом аргументе функции ПОИСКПОЗ – B$1. Поэтому на этот раз выполняется поиск значения только по первой строке A$1:E$1 (на этот раз по горизонтали) базы регистрационных данных автомобилей. Определяется номер позиции исходного значения (на этот раз номер столбца исходной таблицы) и возвращается в качестве номера столбца для третьего аргумента функции ИНДЕКС.

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

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

Всем доброго времени суток, подскажите пжл — необходимо заполнить столбец «actual» таблицы данными из листа «1 actual data» используя условия справочника. Весь мозг сломал)) но такой функции не нашел.

Прикрепленные файлы

• Task.xlsx (23.66 КБ)

Пользователь
Сообщений: 23775 Регистрация: 22.12.2012
14.04.2021 20:02:41
Доброго дня.
Такую думаю нужно самому написать.
Можно, но развлечения в этом не вижу, больше гемора.
Пользователь
Сообщений: 8 Регистрация: 14.04.2021
14.04.2021 20:05:37

а что можно самое простое придумать (пробовал суммеслимн чтобы в 1 actual data подтянуть данные из 1 directory, но не получается(()

Пользователь
Сообщений: 23775 Регистрация: 22.12.2012
14.04.2021 20:12:04

Можно сперва в 1 actual data подтянуть названия из директори, затем сделать сводную, а уже затем ВПРой тянуть в целевую таблицу.

Пользователь
Сообщений: 8 Регистрация: 14.04.2021
14.04.2021 20:16:23
а из директори чем тянуть? (я совсем профан в екселе )
Пользователь
Сообщений: 23775 Регистрация: 22.12.2012
14.04.2021 20:16:43
Пользователь
Сообщений: 826 Регистрация: 11.02.2021
14.04.2021 20:21:11

Напомнило мою 20 задачу.
https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=137370&a.
Такая же структура — этажи с комнатами. По комнатам подсчет, а затем вывод по этажам.

Изменено: Marat Ta — 14.04.2021 20:33:09
Пользователь
Сообщений: 23775 Регистрация: 22.12.2012
14.04.2021 20:26:01
Что-то вроде, но не все наименования есть. Суммы/итоги ещё нужно отдельно добавить.
Прикрепленные файлы

• Task.xlsx (33.04 КБ)

Сообщений: 22249 Регистрация: 28.12.2016
Excel 2013, 2016
14.04.2021 20:26:05

массивная
=IF(B6=»»;»»;SUM(SUMIFS(‘1 actual data’!D:D;’1 actual data’!B:B;IF(‘1 directory’!$B$8:$B$51=таблица!B6;’1 directory’!$A$8:$A$51;1=0);’1 actual data’!C:C;INDEX(‘1 directory’!$A$3:$A$4;MATCH(LOOKUP(2;1/($B$1:B5=»»);$A$1:A5);’1 directory’!$B$3:$B$4;)))))

Прикрепленные файлы

• example2341.xlsx (25.3 КБ)

По вопросам из тем форума, личку не читаю.
Пользователь
Сообщений: 8 Регистрация: 14.04.2021
14.04.2021 20:26:43

вопрос — если формулу прописать, можете? ск-ко будет стоить? спасибо

Сообщений: 22249 Регистрация: 28.12.2016
Excel 2013, 2016
14.04.2021 20:27:38

Цитата
Семен Иванов написал: ск-ко будет стоить?

от черт, опять продешевил 🙂
По вопросам из тем форума, личку не читаю.
Пользователь
Сообщений: 8 Регистрация: 14.04.2021
14.04.2021 20:40:44

Цитата
БМВ написал: от черт, опять продешевил 🙂

Пользователь
Сообщений: 2799 Регистрация: 19.02.2020
14.04.2021 20:41:22

Цитата
Семен Иванов написал: заполнить столбец «actual» таблицы

Заполнил первым найденным значением. Не суммой
Прикрепленные файлы

• Task.xlsx (25 КБ)

Пользователь
Сообщений: 8 Регистрация: 14.04.2021
14.04.2021 20:42:44
как научиться писать такие формулы?
Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
14.04.2021 20:45:29
Да просто! Несколько лет активности на форуме
Пользователь
Сообщений: 23775 Регистрация: 22.12.2012
14.04.2021 20:49:54
Я такие не умею..
Пользователь
Сообщений: 14577 Регистрация: 01.01.1970
14.04.2021 20:50:52

Цитата
vikttur написал: Несколько лет активности

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

Программисты — это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!

Формулы: ссылки на данные из других таблиц

Дополнительные сведения о планах и их возможностях см. на странице «Расценки».

Возможности

Кому доступна эта возможность?

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

Проверьте, доступна ли эта функция на платформах Smartsheet Regions и Smartsheet Gov.

Формулы: ссылки на данные из других таблиц

PLANS

• Smartsheet
• Pro
• Business
• Enterprise

For more information about plan types and included capabilities, see the Smartsheet Plans page.

Права доступа

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

Find out if this capability is included in Smartsheet Regions or Smartsheet Gov.

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

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

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

Хотите работать с данными в одной таблице? Рекомендуем вместо этого использовать поля сводки по таблице.

Прежде чем создавать межтабличные ссылки

Готовы приступить к работе с межтабличными формулами? Обратите внимание на следующее.

• Вы должны обладать необходимыми разрешениями. См. следующую диаграмму.
• Таблица может содержать до 100 отдельных межтабличных ссылок.
• Диапазон, на который указывает ссылка, может содержать до 100 000 входящих ячеек.
• Ссылки из другой таблицы не поддерживаются следующими функциями: CHILDREN, PARENT, ANCESTORS. Использование ссылки из другой таблицы при работе с этими функциями приведёт к ошибке #UNSUPPORTED CROSS-SHEET FORMULA в ячейке с формулой.

Необходимые разрешения

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

Просмотр данных в исходной таблице и ссылки на эти данные

Вставка формулы в конечную таблицу

Изменение ссылки в формуле

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

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

Прежде чем создавать ссылки на данные

Готовы приступить к работе с межтабличными формулами? Обратите внимание на следующее.

• Таблица может содержать до 100 отдельных межтабличных ссылок.
• Диапазон, на который указывает ссылка, может содержать до 100 000 входящих ячеек.
• Ссылки из другой таблицы не поддерживаются следующими функциями: CHILDREN, PARENT, ANCESTORS. Использование ссылки из другой таблицы при работе с этими функциями приведёт к ошибке #UNSUPPORTED CROSS-SHEET FORMULA в ячейке с формулой.

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

Остались вопросы?

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

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

Добавление данных к существующей таблице

Добавление данных в существующую таблицу и редактирование этих данных в таблице – это важная часть для поддержания актуальной и полноценной ГИС. Добавить данные в таблицы можно несколькими способами.

Копирование и вставка из другого приложения

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

Примечание:

Если вставлено больше столбцов, чем существует в настоящее время, дополнительные столбцы удаляются.

Копирование и вставка из другой таблицы ArcGIS Pro

Вы можете добавлять данные в существующую таблицу, вставляя значения, скопированные из другой таблицы, в тот же или другой проект ArcGIS Pro . Копирование и вставка — это рекомендуемый рабочий процесс для обновления и замены существующих значений новой информацией. Если вставлено больше строк, чем на текущий момент существует в таблице базы данных, автоматически будут созданы дополнительные строки. Однако если вставлено больше столбцов, чем существует в настоящее время, дополнительные столбцы удаляются.

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

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

Инструмент Вычислить поле

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

Инструмент Добавить соединение

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

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

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

инструмент Соединение полей

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

При использовании этого рабочего процесса поля записываются в вашу базовую таблицу.

Инструмент Добавить поле

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

Инструмент Присоединить

Используйте инструмент Присоединить , чтобы добавить пространственные объекты или другие данные из нескольких наборов данных в существующий набор данных. Этот инструмент может присоединять точечные, линейные и полигональные классы пространственных объектов; таблицы; растры; каталоги растров; классы пространственных объектов-аннотаций или объектов-размеров к существующему набору данных такого же типа. Например, к имеющейся таблице можно присоединить несколько таблиц, или несколько растров к существующему набору растровых данных, но нельзя соединить линейный и точечный классы пространственных объектов.

Инструмент Вычислить атрибуты геометрии

Инструмент Вычислить атрибуты геометрии добавляет информацию в поля атрибутов объекта, представляющие пространственные или геометрические характеристики и местоположение каждого объекта, такие как длина или площадь и координаты x, y, z и m.

Добавление строк в таблицу

Таблицы атрибутов классов пространственных объектов поддерживают добавление отдельных строк. Для этого щелкните опцию Щелкните, чтобы добавить новую строку в таблице атрибутов.

• Слои аннотаций
• Слои размеров
• Слои мультипатч

Управляет оформлением этой опции во Вкладке Таблица опций проекта.

Вставка строк в автономную таблицу

Вы можете вставить строки в активную автономную таблицу. Нажмите кнопку Вставить строки и введите значение Количество строк для добавления в таблицу. Щелкните Создать или нажмите Enter . Новые строки добавляются в нижнюю часть таблицы, выделяются, и первая вновь добавленная строка отображается посередине.

Примечание:

• За один раз можно добавить не более 1000 строк.
• Если используется определяющий запрос, новые строки могут не появиться.

Дублировать строку в таблице

Вы можете создать дубликат атрибутов объекта или записи. Щелкните правой кнопкой мыши заголовок строки и выберите Дублировать строку , чтобы создать копию выбранной строки в нижней части таблицы. Она выбрана. Геометрия объекта включается при дублировании строки.

Использование Вида Поля для создания, изменения и удаления полей

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

Связанные разделы

• Интерактивный выбор записей в таблице
• Редактирование активной таблицы
• Редактирование значения в ячейке таблицы
• Редактирование атрибутов объектов
• Сортировка записей в таблице
• Фильтрация данных в таблицах
• Опции таблицы

В этом разделе

1. Копирование и вставка из другого приложения
2. Копирование и вставка из другой таблицы ArcGIS Pro