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

"Mon-Sat 9:30am-9:00pm 
Sunday 11:00am-6:00pm"

В крайности этого: 

"Mon-Thurs 9:30am-9:00pm 
Fri & Sat 9:30am-11:00pm
Sunday 9:30am-6:00pm
Stat Hol 11:00am-6:00pm"

Приоритет этой ячейки должен быть в удобочитаемом формате, но мне нужно проанализировать эти данные, чтобы показать, какой час закрывается магазин (всегда 6 вечера, 9 вечера или 11 вечера) для каждого дня недели.

Лучше ли изменить эти данные в формат даты / времени, который нравится Excel, а затем объединить их для удобочитаемой ячейки, или можно (или даже предпочтительнее) проанализировать существующие данные, чтобы получить эти времена закрытия, не меняя формат ?

|источник

3 ответа3

3

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

Чтобы использовать эту формулу, вы должны ввести =ClosingTime($B2) в качестве формулы массива в горизонтальном диапазоне из восьми ячеек. В приведенном ниже примере вы должны ввести формулу в C2 . Затем выберите C2:J2 . Наконец, подтвердите, удерживая ctrl+shift при нажатии enter . Затем вы можете, c2:j2 , перетащить вниз на столько мест, сколько необходимо.

UDF использует регулярное выражение для анализа данных. Если в строке есть два имени дня недели, они заполняют дни между ними одним и тем же временем закрытия; если только один будний день, то он просто заполняет этот будний день.

Для ввода этой пользовательской функции (UDF)alt-F11 открывает редактор Visual Basic. Убедитесь, что ваш проект выделен в окне Project Explorer. Затем в верхнем меню выберите «Вставить / Модуль» и вставьте приведенный ниже код в открывшееся окно.

Option Explicit
Function ClosingTime(S As String)
    Dim RE As Object, MC As Object, M As Object
    Const sPat As String = "(?:(?:\b(mon|tue|wed|thu|fri|sat|sun|hol).*\b(mon|tue|wed|thu|fri|sat|sun|hol))|\b(mon|tue|wed|thu|fri|sat|sun|hol)).*?(\b\d+:?\d*)p"
    Dim arWkDays As Variant
    Dim I As Long, J As Long
    Dim sTemp As String
    Dim V(0 To 7) As Variant

For I = 0 To 7
    V(I) = ""
Next I

arWkDays = VBA.Array("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Hol")

Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .MultiLine = True
    .ignorecase = True
    .Pattern = sPat
    If .test(S) = True Then
        Set MC = .Execute(S)
        For Each M In MC
            With WorksheetFunction
            'submatches 0-1 is range; 2 is single day; 3 is the closing time
            If M.submatches(0) <> "" And M.submatches(1) <> "" Then
                For I = .Match(M.submatches(0), arWkDays, 0) To .Match(M.submatches(1), arWkDays, 0)
                    V(I - 1) = CDate(M.submatches(3) & "PM")
                Next I
            ElseIf M.submatches(2) <> "" Then
                V(.Match(M.submatches(2), arWkDays, 0) - 1) = CDate(M.submatches(3) & "PM")
            End If
            End With
        Next M
    End If
End With

ClosingTime = V

End Function

|источник
2

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

Screenshot1

Объяснение структуры

Столбцы А и В - ваши данные для примера. Обратите внимание, что я включил идентификатор магазина в каждой строке. Это просто хорошая практика на случай, если данные зашифрованы, но не обязательно. Вы хотите, чтобы для каждого магазина было время закрытия по дням, и в магазинах было разное количество записей, поэтому я добавил сводную строку магазина, которая в конечном итоге будет содержать запись для каждого дня. Сводные строки могут быть в отдельном "отчете" вашего вывода.

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

Это также позволяет сохранить несколько записей, используя фразы с наименьшим общим знаменателем. Например, вы использовали трехбуквенные сокращения дня в первом примере, но использовали "чет" во втором. Использование «Mon-Thu» будет соответствовать обоим случаям.

Большинство ваших дневных интервалов переносятся через дефис, но вы использовали «&» для "Пт и Сб". На случай, что там также может быть «Пт-Сб», я просто сделал еще одну запись в списке.

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

На этом скриншоте я временно скрыл некоторые столбцы. Список дневных фраз начинается в столбце N, а дневные фразы - в строке 1. Вы просто добавляете другую фразу в следующий столбец по мере необходимости. Строка 2 содержит список дней, связанных с фразой, с использованием стандартной нумерации дней в Excel. Я сделал праздничный день 0 (я вижу, что он не отображается на моем скриншоте, но S2 будет 0 . Пн / ср / пт будет 246

Первая формула

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

=IF(ISERROR(FIND(N$1,$B3)),"",N$2)

Это ищет дневную фразу заголовка столбца в записи расписания этой строки. Если фраза найдена, ячейка получает включенный список дней из строки 2, в противном случае она пустая. Таким образом, для каждой записи расписания должна соответствовать одна фраза, и это список дней, который будет отображаться для строки.

Вспомогательные колонны

скриншот 2

На этом снимке экрана показаны две вспомогательные колонки, которые упрощают объяснение. Столбец L суммирует список дней. Будет только одна подходящая запись, и это просто объединит ее. Ячейка L3 содержит:

=SUM(N3:AB3)

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

Столбец K содержит время закрытия, извлеченное из записи расписания. Формула в L3:

=IF(ISBLANK(B3),"",REPT(MID(B3,LEN(TRIM(B3))-5,1),IF(MID(B3,LEN(TRIM(B3))-5,1)="1",2,1))&":00pm")

Любой метод синтаксического анализа этого будет беспорядочным, потому что времена имеют разное количество цифр. Эта формула находит цифру единицы времени закрытия. Он использует его один раз, если он равен 6 или 9 и повторяет 1 если время равно 11 . Затем он объединяет «:00 вечера». Я добавил тест ISBLANK, потому что в моем макете есть пустые строки.

Время декодирования по дням

Это подводит нас к сути проблемы.

скриншот 3

На этом скриншоте показана колонка для каждого дня недели плюс праздничные дни. Опять же, существует одна формула, которая используется для всех ячеек, кроме итоговых строк. Ячейка С3 содержит:

=IF(ISERROR(FIND(COLUMN()-3,$L3)),"",$K3)

Мой пример начинается с дней в третьем столбце, и первым из них является Праздники, которые кодируются как день 0. Эта формула вычисляет номер дня, связанный со столбцом, и проверяет, находится ли он в списке дней для этой строки. Если это так, он получает извлеченное значение времени для строки, в противном случае - пустое. Таким образом, каждый день, включенный в фразу дня записи, получает время закрытия записи. Дни, не охваченные графиком для этого ряда, не получают ничего.

Полное ежедневное расписание на неделю - это объединение строк для этого магазина. Если строки сводки находятся в другом месте, вы можете использовать идентификатор хранилища для определения строк, которые входят в каждую сводку.

Для этой демонстрационной концепции мне не понравилась сводная строка. Ячейка С5 - это:

=C3&C4

Ячейка С11 - это:

=C7&C8&C9&C10

Это может быть автоматизировано на основе количества строк, или итоговая строка может быть создана непосредственно вместо консолидации строк компонента.

|источник
1

Я бы использовал Power Query Add-In для этого. В Excel 2016 Power Query встроен в ленту данных в разделе "Получить и преобразовать".

Power Query может начинаться с существующей таблицы Excel. Важный трюк - использовать опцию "Разделить столбец по разделителю", чтобы разделить крайний правый разделитель. Затем вы можете работать в обратном направлении, разбивая временные части строки на новые столбцы времени.

Результат запроса может быть доставлен в виде таблицы Excel. Вы можете создать это без написания макроса или кода функции - вы просто щелкаете мышью в окне Power Query.

Я создал рабочее решение, которое вы можете загрузить с моего OneDrive и попробовать:

http://1drv.ms/1AzPAZp

Это файл: Power Query demo - анализ нестандартных данных даты и времени

|источник

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .