Excel

Как заполнить таблицу в excel используя данные из другой таблицы: Автозаполнение ячеек в 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 (на этот раз по горизонтали) базы регистрационных данных автомобилей. Определяется номер позиции исходного значения (на этот раз номер столбца исходной таблицы) и возвращается в качестве номера столбца для третьего аргумента функции ИНДЕКС.

Скачать пример автозаполнения ячеек из другой таблицы

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

Создание сводной таблицы в Numbers на Mac

Numbers

Поиск по этому руководству

Руководство пользователя Numbers для Mac

  • Добро пожаловать!
    • Основные сведения о приложении Numbers
    • Основные сведения об изображениях, диаграммах и других объектах
    • Создание электронной таблицы
    • Открытие и закрытие электронных таблиц
    • Персонализация шаблонов
    • Использование листов
    • Отмена и повтор изменений
    • Сохранение электронной таблицы
    • Поиск таблиц
    • Удаление электронной таблицы
    • Печать электронной таблицы
    • Изменение фона листа
      • Изменение режима просмотра
      • Настройка панели инструментов
      • Отображение и скрытие боковой панели
      • Изменение настроек
      • Выбор шаблона по умолчанию
    • Панель Touch Bar для Numbers
      • Создание электронной таблицы с помощью VoiceOver
      • Создание формул и автозаполнение ячеек с помощью VoiceOver
    • Добавление и удаление таблицы
    • Выбор ячеек, строк и столбцов
      • Добавление и удаление строк и столбцов
      • Перемещение строк и столбцов
      • Изменение размера строк и столбцов
    • Объединение и разделение ячеек
      • Изменение внешнего вида текста в таблице
      • Отображение, редактирование и скрытие заголовка таблицы
      • Изменение цветов сетки
      • Использование стилей таблицы
    • Изменение размера, перемещение и открепление
      • Добавление текста в таблицы
      • Добавление объектов в ячейки
      • Добавление биржевой информации
      • Автозаполнение ячеек
      • Копирование, перемещение и удаление содержимого ячеек
      • Создание снимка таблицы
      • Форматирование дат, значений валют и других типов данных
      • Создание произвольного формата ячейки
      • Добавление флажков и других элементов управления в ячейки
      • Форматирование таблиц с двунаправленным текстом
    • Перенос текста по словам для размещения в ячейке
    • Выделение ячеек
    • Фильтрация данных
    • Сортировка по алфавиту и сортировка данных в таблице
      • Основные сведения о категориях
      • Добавление, редактирование или удаление категорий
      • Изменение групп категорий
      • Добавление вычислений для обобщения данных группы
      • Основные сведения о сводных таблицах
      • Создание сводной таблицы
      • Добавление и расстановка данных сводной таблицы
      • Изменение параметров сортировки и группировки данных сводной таблицы и многое другое
      • Обновление сводной таблицы
      • Просмотр исходных данных, которые будут использоваться для значений сводной таблицы
    • Быстрое вычисление суммы, среднего значения и других значений
    • Расчет значений по данным в ячейках таблицы
    • Использование справки по формулам и функциям
      • Выбор данных для создания диаграммы
      • Добавление столбчатых, полосчатых, линейных, площадных, круговых, кольцевых или лепестковых диаграмм
      • Добавление пузырьковых диаграмм и диаграмм рассеивания
      • Интерактивные диаграммы
      • Удаление диаграммы
      • Изменение типа диаграммы
    • Изменение данных диаграммы
    • Перемещение и изменение размеров диаграмм
      • Изменение внешнего вида серий данных
      • Добавление легенды, линий сетки и других меток
      • Изменение внешнего вида текста и меток на диаграмме
      • Добавление границы и фона к диаграмме
      • Использование стилей диаграммы
    • Выбор текста и размещение точки вставки
      • Добавление текста
      • Копирование и вставка текста
      • Использование диктовки для ввода текста
      • Использование диакритических знаков и специальных символов
        • Форматирование электронной таблицы для другого языка
        • Использование фонетической транскрипции
        • Использование двунаправленного текста
        • Использование вертикального текста
      • Изменение шрифта или размера шрифта
      • Жирный шрифт, курсив, подчеркивание и зачеркивание
      • Изменение цвета текста
      • Изменение регистра текста
      • Добавление тени или контура к тексту
      • Основные сведения о стилях абзацев
      • Применение стиля абзаца
      • Создание, переименование или удаление стилей абзаца
      • Обновление или возврат стиля абзаца
    • Применение стиля с помощью сочетания клавиш
      • Настройка интервала между символами
      • Добавление буквиц
      • Поднятие и опускание символов и текста
      • Автоматическое форматирование дробей
      • Создание и использование стилей символов
      • Формат тире и кавычек
      • Форматирование текста (китайский, японский, корейский)
      • Установка табуляторов
      • Выравнивание текста
      • Разбиение текста на колонки
      • Настройка межстрочного интервала
    • Форматирование списков
    • Добавление и редактирование уравнений
    • Добавление эффекта выделения в текст
    • Добавление ссылок
    • Добавление линий для разделения текста
      • Добавление изображения
      • Добавление галереи изображений
      • Редактирование изображения
      • Добавление и редактирование фигуры
      • Объединение или разъединение фигур
      • Рисование фигуры
      • Сохранение фигуры в медиатеке фигур
      • Добавление и выравнивание текста внутри фигуры
    • Добавление линий и стрелок
    • Анимация, публикация и сохранение рисунков
      • Добавление аудио и видео
      • Запись звука
      • Редактирование аудио- и видеофайлов
    • Выбор формата фильма или изображения
      • Использование линеек
      • Размещение и выравнивание объектов
      • Использование направляющих линий
      • Размещение объектов внутри текстового блока или фигуры
      • Наложение, группировка и закрепление объектов
    • Изменение прозрачности объектов
    • Заливка объектов цветом или изображением
    • Добавление границы объекта или листа
    • Добавление подписи или заголовка
    • Добавление отражения или тени
    • Использование стилей объектов
    • Изменение размеров, поворот и зеркальное отражение объектов
    • Поиск определения слова
    • Поиск и замена текста
    • Автоматическая замена текста
    • Проверка правописания
    • Задание имени автора и цвета комментариев
    • Выделение текста цветом
    • Добавление и печать комментариев
    • Отправка таблицы
    • Основные сведения о совместной работе
    • Приглашение к совместной работе
    • Совместная работа над общей электронной таблицей
    • Просмотр последних действий пользователей в общей таблице
    • Изменение настроек общей электронной таблицы
    • Закрытие общего доступа к электронной таблице
    • Общие папки и совместная работа
    • Использование Box для совместной работы
    • Использование iCloud с приложением Numbers
    • Импорт таблицы Excel или текстового файла
    • Экспорт в Excel или другой формат
    • Уменьшение размера файла таблицы
    • Сохранение большой электронной таблицы в виде файла пакета
    • Восстановление более ранней версии электронной таблицы
    • Перемещение электронной таблицы
    • Блокировка электронной таблицы
    • Защита электронной таблицы паролем
    • Создание собственных шаблонов и управление ими
      • Перенос таблиц с помощью AirDrop
      • Передача электронных таблиц с помощь Handoff
      • Перенос таблиц через Finder
    • Сочетания клавиш
    • Символы сочетаний клавиш
  • Авторские права

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

Исходные таблицы для сводных таблиц должны:

  • содержать как минимум одну ячейку с данными, которая не является ячейкой заголовка;

  • не содержать объединенные ячейки, охватывающие смежные строки.

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

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

Создание сводной таблицы

  1. Выберите таблицу или диапазон ячеек в электронной таблице.

    Совет. Чтобы быстро добавить сводную таблицу на новый лист, выберите таблицу, затем выберите «Сводная таблица»  в панели инструментов.

  2. В строке меню Numbers вверху экрана выберите «Упорядочение» > «Создать сводную таблицу», затем выберите один из следующих вариантов.

    • На новом листе. Создание сводной таблицы на новом листе с использованием целой таблицы в качестве исходных данных.

    • На текущем листе. Создать сводную таблицу на текущем листе, используя всю таблицу в качестве исходных данных.

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

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

Созданная сводная таблица остается пустой, пока Вы не добавите поля. См. раздел Добавление и расстановка данных сводной таблицы в Numbers на Mac.

Изменение диапазона ячеек исходных данных

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

  1. Выберите сводную таблицу, затем на вкладке «Параметры сводки» в боковом меню «Упорядочение»  нажмите диапазон ячеек в разделе «Источник».

  2. Измените диапазон, затем выберите .

Удаление сводной таблицы

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

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

Максимальное количество символов: 250

Не указывайте в комментарии личную информацию.

Максимальное количество символов: 250.

Благодарим вас за отзыв.

Заполнить таблицу на основе данных другой таблицы

t0ny84
Обычная доска