Страницы: 1
RSS
Функция для поиска и переноса значений из таблицы в таблицу за тремя параметрами
 
Здравствуйте!

Стараюсь задать функцию для автоматического переноса цифровых значений столбцов "П" и "Ф" Таблицы1 в соответствующие им ячейки Таблица2

Я пробовал использовать INDEX+MATCH, задавая поиск адреса пересечения через сопоставление значений YEAR и MONTH из поля Дата в Таблицы2, с соответствующими Таблицы1. Но в итоге срабатывает перенос только первого столбца Январь, а  начиная с Февраль я получаю ошибку. Просмотр исполнения функции говорит, что компилятор останавливается после поиска адреса в поле MONTH, который потом не может сопоставить с общим диапазоном ячеек указанного в INDEX массива.

Прошу опытных форумчан помочь!
Изменено: oocomw - 25.12.2021 16:55:42
 
Код
=ДАТАМЕС(ДАТАЗНАЧ("01.01.2018");ЦЕЛОЕ(СТРОКА(A3)/3)+(ОСТАТ(СТРОКА(A3);3)+1)*12-13)
=ИНДЕКС($C$4:$Z$6;ОСТАТ(СТРОКА(A3);3)+1;ЦЕЛОЕ(СТРОКА(A3)/3)*2-1)
=ИНДЕКС($C$4:$Z$6;ОСТАТ(СТРОКА(A3);3)+1;ЦЕЛОЕ(СТРОКА(A3)/3)*2)
 
Михаил Л, — браво, мастерски!!  :idea:

Сейчас стараюсь разобраться во всех использованных в составлении этих формул агрументах..
Я искренне удивлен, как вы к пришли к необходимости использования даты как числа?
Очень интересно! Отличное решение!

Благодарю!
Изменено: oocomw - 25.12.2021 18:55:38
 
Цитата
oocomw написал:
Сейчас стараюсь разобраться
Так наверное понятнее станет
 
oocomw, вариант в PQ:
Код
let
  src = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
  headersLst = List.Buffer ( Table.ToColumns ( Table.FirstN ( src, 2 ) ) ),
  headersCnt = List.Count ( headersLst ),
  headers = List.Generate (
    () => [ k = 0, header = "Дата" ],
    each [k] < headersCnt,
    each [
      k = [k] + 1,
      header =
        if headersLst{k}{0} = null then
          Date.ToText ( Date.From ( headersLst{k - 1}{0} ), "MM" ) & "|" & headersLst{k}{1}
        else
          Date.ToText ( Date.From ( headersLst{k}{0} ), "MM" ) & "|" & headersLst{k}{1}
    ],
    each [header]
  ),
  rename = Table.FromColumns ( Table.ToColumns ( Table.Skip ( src, 2 ) ), headers ),
  unpivot = Table.UnpivotOtherColumns ( rename, { "Дата" }, "Атрибут", "Значение" ),
  transform = Table.FromRecords (
    Table.TransformRows (
      unpivot,
      ( r ) =>
        Record.TransformFields (
          r,
          {
            { "Дата", ( x ) => #date ( Date.Year ( Date.From ( x ) ), Number.From ( Text.BeforeDelimiter ( r[Атрибут], "|" ) ), 1 ) },
            { "Атрибут", ( x ) => Text.AfterDelimiter ( x, "|" ) }
          }
        )
    ),
    Value.Type ( unpivot )
  ),
  pivot = Table.Pivot ( transform, List.Distinct ( transform[Атрибут] ), "Атрибут", "Значение", List.Sum ),
  typed = Table.TransformColumnTypes ( pivot, { { "Дата", type date }, { "П", type number }, { "Ф", type number } } )
in
  typed
Изменено: surkenny - 25.12.2021 20:58:17
 
surkenny, благодарю, очень опрятно смотрится!
Ранее я никогда Power Query не использовал — открыли мне глаза!

Цитата
Михаил Л, написал: понятнее станет
Уже перечитал талмуды Excel по каждой использованной Вами формуле. Я понял, что программа переводит дату в число, и уже тогда производит с ним исчисления, но...

Но мне не хватает некоторой мысленной связки, дабы уловить логику использования Вами именно операций с ячейками "А3":
Код
=EDATE(DATEVALUE("01.01.2018");INT(ROW(A4)/3)+(MOD(ROW(A4);3)+1)*12-13)
=INDEX($C$4:$Z$6;MOD(ROW(A3);3)+1;INT(ROW(A3)/3)*2-1)
=INDEX($C$4:$Z$6;MOD(ROW(A3);3)+1;INT(ROW(A3)/3)*2)
Это для того, чтобы ввести число в формулу? Но почему именно это число.. Логично, когда в INDEX, — это позиция искомого значения, и я понимаю, что INT(ROW(A3)/3)*2-1) это на одну ячейку левее. Но вот как понять, уловить суть, почему именно эти цифры, и эта ячейка, и операции с ней. Чтобы так же мастерски мог повторить Ваше решение и в случае других задач. Уж очень мне понравилось!

Благодарю!
 
Цитата
oocomw написал:
Я понял, что программа переводит дату в число, и уже тогда производит с ним исчисления, но...
DATEVALUE - лишнее EDATE ожидает число и сама преобразует текст.
По вопросам из тем форума, личку не читаю.
Страницы: 1
Наверх