Блог

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

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

ARRAYFORMULA(MAX(IF(Transaction!$B:$B="Apple",ROW(Transaction!$B:$B),-1))) --> returns 6 ‘

Но я, похоже, не могу добавить в эту формулу, чтобы получить последнюю строку на основе элемента, и если дата меньше или равна дате моего графика

ТАБЛИЦА ТРАНЗАКЦИЙ

ТАБЛИЦА ДИАГРАММ

Чтобы решить вашу проблему с помощью простой формулы, я бы использовал sumifs функцию:

 =sumifs($D:$D,$C:$C,I$15,$B:$B,$H17) I16
 

Вот как выглядят данные Gsheet:

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

• В электронной таблице выберите Инструменты > Редактор сценариев>, чтобы открыть сценарий, привязанный к вашему файлу.
• Скопируйте эту функцию в редактор сценариев и сохраните проект (проверьте встроенные комментарии для получения дополнительной информации о том, что делает функция).:

 function getTable(values) {
  let times = values.map(row => row[0].getTime());
  const minTime = Math.min(...times); // Get first day in series
  const maxTime = Math.max(...times); // Get last day in series
  times = getTimes(minTime, maxTime); // Get all days between minimum and maximum
  const items = [...new Set(values.map(row => row[1]))]; // Get unique list of items
  let targetValues = [["Date", ...items]]; // Header row
  for (let i = 0; i < times.length; i  ) { // Loop through all days
    const time = times[i];
    const dayRows = values.filter(row => row[0].getTime() === time); // Get rows with this day
    if (dayRows) {
      const rowValues = items.map((item, j) => { // Loop through all items in day rows
        const foundRow = dayRows.find(row => row[1] === item); // Find row corresponding to day and item
        const previous = i === 0 ? 0 : targetValues[targetValues.length - 1][j   1]; // Value in previous row
        if (foundRow) return foundRow[2]   previous;
        else return 0   previous;
      });
      targetValues.push([new Date(time), ...rowValues]);
    } else { // If day is not in source data, values are the same than previous row
      targetValues.push([new Date(time), ...targetValues[targetValues.length - 1].slice(1)]);
    }
  }
  return targetValues;
}

function getTimes(minTime, maxTime) {
  let allTimes = [minTime];
  let currentTime = minTime;
  while (currentTime < maxTime) {
    let date = new Date(currentTime);
    date.setDate(date.getDate()   1);
    currentTime = date.getTime();
    allTimes.push(currentTime);
  }
  return allTimes;
}
 

• Теперь, если вы вернетесь к своей электронной таблице, вы можете использовать эту функцию getTable , как если бы вы использовали обычную формулу листов. Вам просто нужно указать соответствующий диапазон в качестве аргумента (в данном случае A2:E7 ), как вы можете видеть здесь:

Ссылка:

• Пользовательские функции в Google Таблицах

Я могу предложить формулу в два этапа.

1. Transactions вкладка:

2. Pivot вкладка, ячейка A1 :

=query({Transactions!A:F},"select Col2,max(Col4) where Col1 is not null group by Col2 pivot Col3",1)

3. ChartData вкладка, ячейка A1 :

 =arrayformula(
 array_constrain(
  {Pivot!A1:D1;
   (sequence(max(row(Pivot!A:A))-1,1,min(Transactions!$B:$B))),
   sumif(row(Pivot!B2:B),"<="amp;row(Pivot!B2:B),Pivot!B2:B),
   sumif(row(Pivot!C2:C),"<="amp;row(Pivot!C2:C),Pivot!C2:C),
   sumif(row(Pivot!D2:D),"<="amp;row(Pivot!D2:D),Pivot!D2:D)
  },
 counta(Pivot!A:A) 1,counta(Pivot!1:1)))