Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
С помощью одной формулы найти значение на даты., Нужно по дате найти сумму.
 
#1
13.04.2022 09:54:20
Доброго времени суток! второй день не даёт покоя эта задачка. Нашел эту задачку в интернете. Всё что мог перепробовал.
 
 
 
#2
13.04.2022 10:26:27
Код
=ИНДЕКС(БД!C$5:AG$52;ПОИСКПОЗ(ГОД(B6);БД!A$5:A$52;)+МЕСЯЦ(B6)-1;ДЕНЬ(B6))
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#3
13.04.2022 10:33:15
Спасибо!
 
 
 
#4
13.04.2022 10:48:38
Максим Юрков, В идеале Ваша задача решается либо через PQ, либо макрос+сводная.
Мой канал
 
 
 
#5
13.04.2022 11:37:44
skais675, в задании написано по-другому :) Хотя PQ + PP тут идеальный вариант. Да даже PQ не нужен, просто исходные данные собирать в плоско виде.
Вероятно, составитель задания сам не обладает знаниями о подобных инструментах :)
Теперь по делу:
1. Данные на 31 июня 2024 - это нормально? :) Вот еще одно доказательство, что "данные" не надо в сводной хранить.

2. PQ:
data
Код
let
  src = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
  unpivot = Table.UnpivotOtherColumns ( src, { "Год", "Месяц" }, "День", "Значение" ),
  addDate = Table.CombineColumns (
    unpivot,
    { "Год", "Месяц", "День" },
    ( x ) => Date.FromText ( Text.From ( x{0} ) & " " & x{1} & " " & x{2}, "RU-ru" ),
    "Дата"
  ),
  delErrors = Table.RemoveRowsWithErrors ( addDate, { "Дата" } ),
  typed = Table.TransformColumnTypes ( delErrors, { { "Дата", type date }, { "Значение", type number } } )
in
  typed

calendar
Код
let
  startDate   = Date.StartOfYear ( List.Min ( data[Дата] ) ),
  endDate     = Date.EndOfMonth ( List.Max ( data[Дата] ) ),
  allDatesLst = List.Dates ( startDate, Duration.TotalDays ( endDate - startDate ) + 1, #duration ( 1, 0, 0, 0 ) ),
  datesTbl    = Table.FromColumns ( { allDatesLst }, type table [ Дата = date ] ),
  addYM       = Table.AddColumn ( datesTbl, "ГодМесяц", each Number.From ( Date.ToText ( [Дата], "yyyyMM" ) ), Int64.Type ),
  YMList      = List.Buffer ( List.Sort ( List.Distinct ( addYM[ГодМесяц] ) ) ),
  addPI       = Table.AddColumn ( addYM, "Индекс периода", each List.PositionOf ( YMList, [ГодМесяц] ) + 1, Int64.Type ),
  addY        = Table.AddColumn ( addPI, "Год", each Date.Year ( [Дата] ), Int64.Type ),
  addQ        = Table.AddColumn ( addY, "Квартал", each Date.QuarterOfYear ( [Дата] ), Int64.Type ),
  addM        = Table.AddColumn ( addQ, "Месяц№", each Date.Month ( [Дата] ), Int64.Type ),
  addMN       = Table.AddColumn ( addM, "Месяц", each Date.MonthName ( [Дата], "ru-RU" ), type text ),
  addSMN      = Table.AddColumn ( addMN, "Мес", each Date.ToText ( [Дата], "MMM" ), type text ),
  addD        = Table.AddColumn ( addSMN, "Число", each Date.Day ( [Дата] ), Int64.Type ),
  addWD       = Table.AddColumn ( addD, "День недели№", each Date.DayOfWeek ( [Дата] ) + 1, Int64.Type ),
  addWDN      = Table.AddColumn ( addWD, "День недели", each Date.DayOfWeekName ( [Дата] ), type text )
in
  addWDN

3. PP: sumAmount:=SUM ( 'data'[Значение] )
Изменено: surkenny - 13.04.2022 11:39:47
 
 
 
#6
13.04.2022 11:44:07
Я конечно благодарен, но до такого уровня пока ещё рановато. :sceptic:  
Изменено: Максим Юрков - 13.04.2022 11:44:57
 
 
 
#7
13.04.2022 11:48:47
Максим Юрков, сложного тут ничего нет. Кроме даты из колонок года/месяца/дня все кнопками сделать можно :)
Просто посмотрите на форуме в приемах про сводные таблицы, чтобы суть понять. А в PQ мы всего лишь двумерную таблицу в плоскую трансформируем.
 
 
 
#8
13.04.2022 11:49:22
Максим Юрков,  Ну Вы смогли бы просто сразу организовывать правильно данные в виде плоской таблицы, а далее сводная. Тут больших знаний не требуется. А вот то как Вы пытаетесь выкручиваться - это уже искусство.
Мой канал
 
 
 
Страницы: 1
Наверх