Условный поиск в Excel

Question

Я хочу использовать Excel для сравнения пар чисел из столбца «Pre /Post» следующих данных:

Student       Course          Pre/Post    Score
K300997203  FHS120100417    Pre         3
L286197217  FHS120100417    Pre         5
S106497203  FHS120100417    Pre         4
K300997203  FHS120100417    Post        4
L286197217  FHS120100417    Post        4
S106497203  FHS120100417    Post        4
S106497203  FHS220100424    Pre         4

В частности, я хочу, чтобы ячейка содержала разницу значений в столбце "Оценка", где "Pre" и "Post" появляются соответственно в строках с одинаковым значением в столбцах "Student" и "Course".

Например, у ученика K300997203 есть строка, содержащая курс FHS120100417, 3 балла и "Pre"

А ТАКЖЕ

Студент K300997203 имеет строку, содержащую курс FHS120100417, оценку 4 и "сообщение".

Как я могу вычислить значение ячейки как оценку в строке, содержащей "Post", минус оценку в строке, содержащей "Pre"?

Answer 1

С вашими данными, начинающимися в A1, номером студента в A11 и курсом в B11, используйте эту формулу

=SUMPRODUCT(($A$2:$A$8=A11)*($B$2:$B$8=B11)*($C$2:$C$8="Post")*($D$2:$D$8))-SUMPRODUCT(($A$2:$A$8=A11)*($B$2:$B$8=B11)*($C$2:$C$8="Pre")*($D$2:$D$8))

Answer 2

Это идеальное место для сводного стола. (Инструкции предполагают Excel 2007)

• Выберите ваши данные, а затем вставьте сводную таблицу
• Добавьте курс, студента и Pre/Post к меткам строк в указанном порядке
• Добавить счет к значениям
• Выберите любую ячейку в сводной таблице, которая содержит «Pre» или «Post»
• Перейти к формулам -> Расчетный элемент ...
• Выберите Pre/Post из списка полей
• В поле Имя введите Delta
• В поле Формула введите = Пост-Pre
• Нажмите OK - теперь вы увидите, что значения Delta появляются в ваших сводных данных
• Нажмите стрелку вниз рядом с метками строк и отмените выбор Pre и Post из списка.
• Настройте Total и Subtotals на свой вкус (в этом случае я рекомендую отключить их.)

(Спасибо, что задали этот вопрос - вы дали мне возможность узнать разницу между вычисляемыми полями и рассчитанными элементами в сводных таблицах :)

РЕДАКТИРОВАТЬ: я должен добавить, что если вы сначала преобразуете свои данные в список, то вы можете легко добавить данные к нему и нажать обновить в сводной таблице, чтобы вычислить и отобразить новые данные.