Как мне выделить разные текстовые ячейки в строке, а затем суммировать эту строку

Question

У меня есть настройка электронных таблиц для контроля отсутствия в офисе. Столбцы - это дни месяца с 1 по 31, а строки - месяц / сотрудники.

        Mon Tue Wed Thu Fri

1 января 3 3 4 Жанна
январь
Бен
Ellie
Алиса
Stef
Виктория
февраль
Жанна
январь
Бен
Ellie
Алиса
Stef
Виктория

Отсутствия перечислены как AL, AL.5, SL & SL.5 = ежегодный отпуск, годовой отпуск на полдня, отпуск по болезни и отпуск на больничный полдня

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

У меня проблемы с тем, чтобы любая формула распознала AL.5 и SL.5 как значение .5. Я попытался добавить таблицу критериев, но не могу заставить формулу работать.В настоящее время я использую Excel 2013.

Answer 1

Это немного ловко, но при условии, что ваши четыре кода для типов отпуска фиксированы, это должно служить вашим целям:

Текст в ячейке H2 - это формула I, введенная в ячейку F2, а затем скопированная в диапазон F2:G4. Первый COUNTIF подсчитывает количество вхождений соответствующего кода "AL" или "SL", а второй подсчитывает количество кодов «AL.5» или «SL.5». Я просто умножил второй счет на 0,5, чтобы получить взвешенную сумму, которую вы ищете.

Answer 2

Я бы использовал Power Query Add-In для этого. Power Query может импортировать данные из таблицы Excel. Я бы использовал команду Unpivot для преобразования каждой ячейки в строку с одним столбцом значений, содержащим все значения отсутствий (например, AL, SL.5). Затем я использовал бы команду Split в этом столбце, чтобы отделить первые 2 символа (AL, SL) от значений .5. Я бы переименовал столбец с первыми 2 символами в тип отсутствия, а новый столбец - в дни. Затем я использовал бы команду Replace, чтобы заменить любые пустые значения в столбце Days на 1. Затем я бы преобразовал тип данных столбца Days в десятичное число.

На этом этапе вы можете использовать команду «Группировать по» для суммирования данных и столбца «Сумма дней». Я, вероятно, просто доставил бы запрос в подробном виде в модель данных Excel, а затем построил бы сводную таблицу поверх него. Это даст вам больше аналитических возможностей, поскольку вы можете свободно суммировать, фильтровать, нарезать кубиками и т.д.