Excel - формула для горизонтальных данных без VBA / Macros?

excel excel-formula

66 просмотра

3 ответа

1405 Репутация автора

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

CELL1   CELL2       CELL3       CELL4       CELLn

ITEM    # MATERIAL  # MATERIAL  # MATERIAL

Примером этого может быть:

A       B           C           D           n

Tacos   5 Tortillas 6 Garlic    4 Onions    3 Peppers

Banana  // These cells are empty because Bananas need no ingredients.

Pasta   2 Garlic    3 Sauce

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

Torillas    5
Garlic      8
Onions      4
Peppers     3
Sauce       3

Если бы это был язык программирования, я мог бы сделать это легко с помощью нескольких циклов for, но просто используя IFи, COUNTIFи SUMя просто не знаю, с чего начать.

Автор: Hatefiend Источник Размещён: 16.07.2016 06:45

Ответы (3)


0 плюса

3697 Репутация автора

Предполагая, что A2: E4 содержит данные, а A9: A13 содержит ингредиенты, для которых нужно вернуть счет, введите следующую формулу в B9, подтвердите нажатием CONTROL + SHIFT + ENTER и скопируйте:

=SUM(IF(RIGHT($B$2:$E$4,LEN(A9))=A9,LEFT($B$2:$E$4,FIND(" ",$B$2:$E$4)-1)+0))

Отрегулируйте диапазоны соответственно.

Автор: Domenic Размещён: 16.07.2016 07:24

0 плюса

2168 Репутация автора

вот что я могу сделать ..

=SUM(IF(IFERROR(SEARCH(G6,$B$2:$E$4),0)>0,VALUE(LEFT($B$2:$E$4,SEARCH(CHAR(32),$B$2:$E$4)-1)),0))

введите описание изображения здесь

(p / s: я меняю чеснок на макароны до 5, я люблю чеснок)

Автор: Rosetta Размещён: 17.07.2016 07:53

0 плюса

9638 Репутация автора

Решение

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

Итак, сначала я определю всю область с данными ( B2:E4) как именованный диапазон materials. Затем я использую неприятную формулу, чтобы создать 2 вектора, один для элемента и один или количество.

  1. Для количества:
= IF (ISERROR (слева (OFFSET (материалы, ОТБРЫ ((СТРОКА () - СТРОКА ($ A $ 9)) / КОЛОННЫ (материалы)), МО (СТРОКА () - СТРОКА ($ A $ 9), колонки (материалы)) , 1,1), FIND ("", OFFSET (материалы, TRUNC ((ROW () - ROW ($ A $ 9)) / COLUMNS (материалы)), MOD (ROW () - ROW ($ A $ 9), КОЛОННЫ (материалы)), 1,1)))), 0, ЗНАЧЕНИЕ (ЛЕВАЯ (OFFSET (материалы, ОТБРЫ ((СТРОКА () - СТРОКА ($ A $ 9)) / КОЛОННЫ (материалы)), МО (СТРОКА () - ROW ($ A $ 9), КОЛОННЫ (материалы)), 1,1), FIND ("", OFFSET (материалы, TRUNC ((ROW () - ROW ($ A $ 9)) / КОЛОННЫ (материалы)), MOD ( СТРОКА () - СТРОКА ($ A $ 9), колонки (материалы)), 1,1)))))
  1. Для предметов:
= IF (ISERROR (MID (OFFSET (материалы, ОТБР ((СТРОКА () - СТРОКА ($ B $ 9)) / КОЛОННЫ (материалы)), МО (СТРОКА () - СТРОКА ($ B $ 9), колонки (материалы)) , 1,1), FIND ("", OFFSET (материалы, TRUNC ((ROW () - ROW ($ B $ 9)) / COLUMNS (материалы)), MOD (ROW () - ROW ($ B $ 9), КОЛОННЫ (материалы)), 1,1)) + 1, LEN (OFFSET (материалы, ОТБРЫ ((СТРОКА () - СТРОКА ($ B $ 9)) / КОЛОННЫ (материалы)), МО (СТРОКА () - СТРОКА ($ B $ 9), колонки (материалы)), 1,1)))), "", MID (OFFSET (материалы, ОТБР ((ROW () - ROW ($ B $ 9)) / КОЛОННЫ (материалы)), МО (ОСМ () -ROW ($ B $ 9), КОЛОННЫ (материалы)), 1,1), НАЙТИ ("", OFFSET (материалы, TRUNC ((ROW () - ROW ($ B $ 9)) / КОЛОННЫ (материалы)) , MOD (СТРОКА () - СТРОКА ($ B $ 9), колонки (материалы)), 1,1)) + 1, LEN (OFFSET (материалы, ОТБР ((ROW () - ROW ($ B $ 9)) / КОЛОННЫ (материалы)), МО (СТРОКА () - СТРОКА ($ B $ 9), колонки (материалы)), 1,1))))

Вы вставляете его в первую ячейку соответствующего вектора, затем изменяете все ссылки на ячейки (все $A$9, и $B$9) на ячейку, в которую вы только что вставили формулу (каждая формула по отношению к своей собственной ячейке). Сохраняйте абсолютные ссылки . Наконец, вы перетаскиваете его вниз до тех пор, пока не достигнете последнего элемента в данных (чтобы ваши векторы имели длину ROWS(materials)*COLUMNS(materials)).

Теперь вы просто добавляете сводную таблицу к этим векторам и отфильтровываете пустые ячейки.

Суммирование по пунктам

Подскажите, если это решило проблему;)

PS Также, если вы хотите, чтобы я объяснил немного больше об этом, напишите мне в комментариях

Автор: EBH Размещён: 19.07.2016 07:43
Вопросы из категории :
32x32