0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как ВПР затем умножить в таблицах Excel?

ВПР с умножением, используя коллекция/словарь или классы

Моя проблема заключается в следующем: у меня есть пробный баланс (список счетов, охватывающий несколько месяцев с их конечными остатками в разных валютах) и другой лист, содержащий курсы обмена иностранной валюты на доллары США за эти месяцы. Первый рабочий лист может иметь сотни тысяч строк, а второй рабочий лист — около 150-200 строк. Сокращенная выдержка из этих двух листов может выглядеть следующим образом:

Моя цель заключается в том, чтобы:

  • Заполните колонку D листа 1, умножив колонку C на значение обменного курса в колонке C листа 2 за соответствующий месяц. Так, в D1 я хотел бы узнать курс евро на 18 декабря (0,9) и умножить 12000 на это значение. Однако для D4 я хотел бы узнать курс евро на 19 марта (0,85) и умножить 60000 на это значение
  • Заполнить колонку E листа 1, заранее выбрав месяц, для которого я хочу использовать курс обмена (независимо от месяца, указанного в колонке A листа 1). Так, например, я могу перейти к определенной переменной «Dec-18» и затем использовать только курсы валют за Dec-18 для всего списка элементов на листе 1. Поэтому в этот раз в E2 я умножу 12000 на 0,9 (как указано выше), а для E4 я умножу 6000, но снова на 0,9 (а не на 0,85).

Я просмотрел сообщения о vlookup, словарях и классах, но не смог найти ответ на этот вопрос. Может ли кто-нибудь из вас помочь мне найти частичное решение, которое я мог бы улучшить хотя бы частично?

1 ответ

  • Все матчи Мирового тура свободы прессы

Как сделать VRP всех совпадений Если у меня есть ID String 1 xxx 2 yyyy 1 zzz 3 ooo 1 ppp 1 zzz Мне нужна функция VRP и сделать в одной ячейке xxx zzz ppp Application.Vlookup(1;A2:B7;2;False) Найдено только первое вхождение Как найти все уникальные соответствия?

Данные нужно объединить, чтобы заполнить лист, но у меня есть некоторые сомнения. Я хочу добавить данные с другого листа к числу-значению на листе. Вот пример: Я хочу скопировать столбец C на листе 1 с возрастом, который появляется на листе 2 (меня не волнует, копирует ли он все целиком).

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

Эта формула позволяет получить процентную ставку

Затем это значение умножается на колонку C.

Читайте так же:
Как быстро перейти к следующему уникальному значению в столбце Excel?

Чтобы получить результат (используйте эту формулу для столбца D).

Для столбца E формула почти такая же, просто запишите где-нибудь фиксированное значение месяца и используйте эту ячейку в SUMIFS как критерий вместо столбца A.

Ячейка $I$2 (см. красным цветом в примере ниже):

Лист1
enter image description here

Лист2
enter image description here

Похожие вопросы:

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

Я недавно открыл для себя матричные формулы Excel Ctrl+Shift+Enter и все еще изучаю их. Перейдем к моей проблеме, ЛистА: Код продукта S1 19875 S2 19834 S1 13575 S1 35675 S2 47875 ЛистВ.

Я хочу выяснить, есть ли способ использовать vlookup для текстовых значений. В большинстве случаев я преобразовываю текстовые значения в число, а затем выполняю VRP, но это может занять много времени. Например.

Когда у меня есть ID String 1 xxx 2 yyyy 1 zzz 3 ooo 1 ppp 1 zzz мне нужна функция VRP и мне нужно сделать совпадающие данные в одной строке xxx zzz ppp файлы. Vlookup(1;A2:B7;2;False) Найдено.

Я пытаюсь объединить данные, чтобы заполнить лист, но у меня есть некоторые сомнения. У меня есть числовое значение на одном листе, и я хочу заполнить его данными с другого листа. Например: Я хочу заполнить.

В Python 3 эта структура данных выглядит так: foo = [(1, «ADTABC», 7), (3, «ACHDAT», 8), (1, «AQNDXB», 15), (2, «AFTCBA», 14)] Первый и второй элементы каждого кортежа гарантированно уникальны.

У меня проблема с функцией Vlookup, и я не знаю, как ее решить. По какой-то причине я не мог добавить столбцы, поэтому мне пришлось исправить это в Formula. В основном у.

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

Чтобы получить последнюю дату, связанную с ID (есть несколько записей, связанных с каждым ID), мне нужно UPP на ID No:5, а затем вернуть последнюю дату.

Я застрял со сложной матричной формулой vlookup. Пояснение: На одном листе у меня все рабочие места На другом листе у меня все работники. Простая цель, когда у рабочего есть.

Создание связи между двумя таблицами в Excel

Вы когда-нибудь использовали функцию БПФ для переноса данных столбцов из одной таблицы в другую? Поскольку Excel теперь имеет встроенную модель данных, функция VRP устарела. Вы можете создать связь между двумя таблицами на основе соответствующих данных, которые они содержат. Затем можно создавать листы Power View или сводные таблицы и другие отчеты с полями из каждой таблицы, даже если они получены из разных источников. Например, если у вас есть данные о продажах клиентов, вам может понадобиться импортировать и связать данные логики транзакций с течением времени, чтобы проанализировать тенденции продаж по годам и месяцам.

Читайте так же:
Как в Excel привести все листы в альбомную ориентацию?

Все таблицы в книге указаны в списках полей в сводной таблице и в Power View.

Браузер не поддерживает видео.

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

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

Вы можете отформатировать данные в виде таблицы или импортировать внешние данные в виде таблицы на новом.

Дайте каждой таблице уникальное имя: на вкладке Работа с таблицами нажмите Конструктор > Имя таблицы и введите имя.

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

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

Щелкните Данные> Отношения.

Если отношения отсутствуют, в книге содержится только одна таблица.

В окне Управление подключениями нажмите кнопку Создать.

В окне Create Relationship нажмите стрелку рядом с полем Table и выберите таблицу из выпадающего списка. В отношениях «один-ко-многим» эта таблица должна быть частью с несколькими элементами. В примере с клиентами и логикой транзакций во времени, таблица Customer Sales должна быть выбрана первой, так как много продаж, вероятно, происходит каждый день.

Для элемента Column (foreign) выберите столбец, который содержит данные, связанные с элементом Related column (primary key). Например, если в обеих таблицах есть колонка данных, выберите ее сейчас.

Связанные таблицы должны содержать столбец данных, который связан с таблицей, выбранной в поле Table (Таблица).

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

Нажмите на кнопку ОК.

Дополнительные сведения о связях между таблицами в Excel

Примечания о связях

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

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

Читайте так же:
Как быстро извлечь часть даты из ячейки в Excel?

Можно иметь связь один-к-одному (у каждого пассажира один посадочный талон) или один-ко-многим (у каждого рейса много пассажиров), но не многие-ко-многим. Связи «многие-ко-многим» могут привести к ошибкам циклической зависимости типа «Обнаружена циклическая зависимость». Эта ошибка может возникнуть, если вы создадите прямую связь между двумя таблицами с отношением «многие-ко-многим» или если вы создадите косвенные связи (цепочка связей таблиц, в которой каждая таблица связана со следующей отношением «один-ко-многим», но между первой и последней таблицей формируется отношение «многие-ко-многим»). Связи между таблицами в модели данных можно найти в следующем документе.

Между типами столбцов должна быть совместимость. Типы данных в моделях данных предоставляют более подробную информацию.

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

Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании

Вы можете узнать о взаимосвязи между таблицами и логике операций, изучив бесплатные данные в Microsoft Azure Marketplace. Некоторые из этих наборов данных очень большие и требуют быстрого подключения к Интернету, чтобы загрузить их за разумное время.

Выберите Получить внешние данные > Из службы данных Azure > Из Microsoft Azure Marketplace. Microsoft Azure Marketplace откроется после нажатия на Мастер импорта таблиц.

В разделе «Цены» нажмите «бесплатно».

В разделе Category (Категория) нажмите Science & Statistics (Наука и статистика).

Найдите DateStream и нажмите Сохранить.

Введите свои учетные данные Microsoft и нажмите Вход. Откроется предварительный просмотр данных.

Прокрутите страницу вниз и нажмите Выбрать запрос.

Нажмите на кнопку «Далее».

Чтобы импортировать данные, выберите BasicCalendarUS и нажмите Готово. Импорт занимает около минуты, если ваше интернет-соединение быстрое. После того как все будет завершено, вы увидите отчет с перемещенными 73 414 строками. Нажмите Закрыть.

Чтобы импортировать второй набор данных, нажмите Получить внешние данные > Из службы данных > Из рынка Microsoft Azure Marketplace.

В разделе Тип выберите Данные данных.

Нажмите кнопку Free в области Price (Цена).

Найдите задержки рейсов американских авиакомпаний и нажмите Select.

Прокрутите вниз и нажмите кнопку Выбрать запрос.

Нажмите на кнопку «Next» (Далее).

Завершите импорт данных. При быстром подключении к Интернету импорт займет около 15 минут. После завершения в отчете будет показано состояние 2 427 284 строк. Нажмите кнопку Закрыть. Теперь у вас есть две таблицы в модели данных. Чтобы связать их, вам нужны совместимые столбцы в каждой таблице.

Читайте так же:
Как вернуться к предыдущей версии сохранения в Excel?

Убедитесь, что значения в столбце DateKey в таблице BasicCalendarUS имеют формат 01.01.2012 00:00:00. Таблица On_Time_Performance также имеет столбец даты и времени FlightDate со значениями в том же формате: 01.01.2012 00:00:00. Эти два столбца содержат совпадающие данные одного типа, и по крайней мере один из столбцов (DateKey) содержит только уникальные значения. С помощью этих столбцов будут созданы связи между этими таблицами.

В окне Power Pivot нажмите Crosstab, чтобы создать перекрестную таблицу на новом или уже существующем листе.

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

После расширения таблицы BasicCalendarUS добавьте таблицу BasicCalendarUS в диапазон.

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

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

Добавьте On_Time_Performance в поле связанной таблицы и FlightDate в поле связанной колонки (первичный ключ).

ВыберитеBasicCalendarUS в поле Table и выберите DateKey в поле Column (foreign). Нажмите OK, чтобы создать взаимосвязь.

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

В таблице BasicCalendarUS перетащите ключ года в область строки, расположенную над элементом MonthInCalendar.

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

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

Таблица BasicCalendarUS должна быть открыта в окне программы Power Pivot.

Нажав кнопку Сортировать по столбцам, можно отсортировать главную таблицу.

В поле «Сортировать» выберите MonthInCalendar.

В поле Город выберите месяц года.

Каждая комбинация «месяц и год» (октябрь 2011, ноябрь 2011) теперь сортируется по номеру месяца года (10, 11). Изменить порядок сортировки несложно, поскольку канал DateStream предоставляет все необходимые столбцы для работы этого сценария. Ваши действия будут отличаться, если вы используете другую таблицу временной логики.

«Могут потребоваться связи между таблицами»

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

Кнопка "Создать", отображаемая при необходимости создать связь

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

Шаг 1. Определите, какие таблицы указать в связи

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

Читайте так же:
Как всегда в ячейке A1 при открытии книги?

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

Использование неоднозначных связей в сводной таблице или отчете Power View может привести к неточным результатам. Если все ваши таблицы каким-то образом связаны друг с другом, и вы пытаетесь объединить поля из разных таблиц, вы получите сообщение «Могут потребоваться взаимосвязи.». Скорее всего, ваша встреча была связана по принципу «многие ко многим». Связи между таблицами, которые соединяются с нужными вам таблицами, почти наверняка будут иметь более двух связей «один ко многим». Не существует простого обходного пути, который работает в любой ситуации, но вы можете попробовать создать вычисляемые столбцы, чтобы объединить столбцы, которые вы хотите использовать в одной таблице.

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

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

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

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

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

Типы данных столбца подстановки и исходного столбца должны быть совместимы. О типах данных читайте в разделе Модели данных.

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector