Генерировать или заполнять данные ячейки на основе другого набора данных excel

У меня есть набор данных, который показывает;

  • Имя сотрудника
  • Дата
  • начала работу
  • закончилась работа

Набор данных

Теперь я пытаюсь получить отчет как лист, где я могу выбрать определенное имя сотрудника из списка сотрудников, чтобы просмотреть его / ее время, посещаемое в течение определенного месяца.

доклад

Я пробовал vlookup, но не пошел туда, поскольку мне нужно искать два столбца плюс ряд.

Это возможно? без макросов или vba. благодаря

Поскольку имя и дата являются уникальными идентификаторами, можно использовать функцию sumifs.

Для «времени в» и «Рейчел» это будет выглядеть следующим образом:

=Sumifs(column 'time in' from data set, column 'name' from dataset, “Rachel”, column 'date' from data set, “10/01/2017”) 

Где Rachel и дата также могут быть ссылочной ячейкой.

 =AGGREGATE(15,6,ROW(SHEET1!$A$2:$E$22)/((SHEET2!$B$1=SHEET1!$B$2:$B$22)*(SHEET2!$A4=SHEET1!$C$2:$C$22)),1) 

Вышеприведенная формула захватит номер строки, соответствующий вашим критериям. чтобы вытащить нужную информацию, вы можете поместить номер строки в формулу INDEX, чтобы получить следующее:

 =INDEX(SHEET1!$D:$E,AGGREGATE(15,6,ROW(SHEET1!$A$2:$E$22)/((SHEET2!$B$1=SHEET1!$B$2:$B$22)*(SHEET2!$A4=SHEET1!$C$2:$C$22)),1),COLUMN(A1)) 

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

 =IFERROR(INDEX(SHEET1!$D:$E,AGGREGATE(15,6,ROW(SHEET1!$A$2:$E$22)/((SHEET2!$B$1=SHEET1!$B$2:$B$22)*(SHEET2!$A4=SHEET1!$C$2:$C$22)),1),COLUMN(A1)),"Nothing found") 

если вы предпочтете пробел, а ничего не увидите, измените отображение «ничего не найдено» на «» или 0, если вы хотите отобразить 0.

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

Я думаю, что сводная таблица сделает вам работу.

  1. Поместите имя сотрудника в фильтр , укажите дату и время в строках.
  2. Удалить промежуточные итоги из сводной таблицы
  3. Изменить таблицу таблицы на таблицы и повторить строки
  4. Щелкните правой кнопкой мыши на Time In и выберите Ungroup

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

Затем у вас есть изображение ниже.

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

У меня есть следующий макет:

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

В B11 напишите эту формулу и перетащите вниз:

 =INDEX($B$2:$E$5,MATCH($B$7&$A11,$B$2:$B$5&$C$2:$C$5,0),3) 

В C11 напишите это и перетащите вниз:

 =INDEX($B$2:$E$5,MATCH($B$7&$A11,$B$2:$B$5&$C$2:$C$5,0),4) 

Обратите внимание, что это Array-Formulas, поэтому вам нужно ввести их с помощью CTRL + SHIFT + ENTER вместо обычного Enter .

Вы получите ошибку #NV, если сотрудник не работал в одну из дат A11 и A12. Таким образом, вы можете окружить Формулу с помощью IFERROR чтобы избежать этого.

  • Могу ли я сделать структурированную ссылку абсолютной в excel 07?
  • Как найти значение с несколькими критериями в Excel 2007 и более поздних версиях
  • Vlookup, чтобы найти значения, относящиеся к ссылочной ячейке, и суммировать их
  • Группировка по идентификатору, чтобы найти, есть ли изменения в другом столбце
  • Удаление определенного символа, если он появляется в конце ячейки?
  • Логика формулы для извлечения данных конкретных ячеек
  • Использование SUMPRODUCT, но исключить возврат, если ячейка критериев пуста
  • Контрольная ячейка Excel для сопоставления строк с несколькими другими ячейками
  • Среднее из 10 лучших значений в строке, обрабатывающих пустые ячейки как единицы
  • Попытка написать IF (Match); возможная синтаксическая ошибка?
  • Формула Excel: ввод только 4 цифр Excel конвертирует в формат даты dd / mm / yyyy
  • Давайте будем гением компьютера.