Страницы: 1
RSS
Сравнение двух диапазонов, выделение совпадающих элементов и объединение их в одну строку с разделителем (не массивом)
 
Добрый вечер, знатоки Экселя!

Прошу помощи!
Пытаюсь автоматизировать составление табеля, получилось почти всё, но зависла на одном моменте: случается так, что у одного и того же человека в течение месяца бывает два, а то и три типа неявок на работу (например, больничный, командировка и дни без сохранения з/п).

Один тип считать научилась, но на двух-трёх застряла. По сути, я вижу в процессе вычисления СУММПРОИЗВ почти то, что мне нужно, а именно подсчитанное количество по каждому типу неявки, но сумма мне не нужна, а нужна запись, как в жёлтых ячейках приложенного файла: во-первых, сами эти неявки через слеш, во-вторых, их суммы по человеку, записанные так же.

То есть, необходимо:
0. Найти, есть ли вообще какие-то типы неявок по человеку (это сделала)
1. Найти, какие именно типы есть (пока считается только один тип)
2. Записать типы через слеш, если их больше одного
3. Посчитать количество найденных типов, записать также через слеш (необязательно, с этим я справлюсь сама)


Возможно ли это вообще реализовать чисто формулами?

Нужные формулы в столбце AO (Кол-во неявок (дн)).
Именованный диапазон "неявки" на листе "справочники" (мало ли, вдруг нужно будет...)
Эксель 2013, комп старый и слабый.
 
Добрый вечер.
Для 2013 Экселя вообще грустно, там нет функций которые могут помочь. Как минимум УНИК() нужно бы...
Я бы писал UDF, и сразу одну на обе ячейки, т.к. по ТЗ нужно связать количество с буквами.
P.S. Вот добавил в файл, там непонятно почему полмесяца, ну ладно, пока на полмесяца...
Изменено: Hugo - 08.10.2024 18:25:57
 
Цитата
написал:
Для 2013 Экселя вообще грустно, там нет функций которые могут помочь.
Эх... Хотела стандартными обйтись...
Спасибо большое!

А можно как-нибудь искать только по тем неявкам, которые в именованном диапазоне "неявки"? Просто "В" - это выходные, и их считать не нужно, так же, как и не указанный в примере, но реально существующий тип "РП" (рабочий праздничный день), они считаются в отдельной колонке (уже реализовано).

В пользовательских функциях и VBA вообще я не сильна, увы...
Цитата
написал:
там непонятно почему полмесяца, ну ладно, пока на полмесяца
Потому что отображение только первой половины месяца или всего зависит от даты составления табеля) Если изменить её на любую после 15 числа, вторая половина месяца тоже отобразится.
 
macovea, в UDF сам себе хозяин, можно что угодно в коде делать, например выкинуть из анализа В и РП прямо в коде, или добавить ещё один аргумент, содержимое которого (массив данных) не учитывать.
Про полмесяца спросил потому что сейчас указание всего месяца для UDF не годится, потому что там в середине лишнее.
Ну а так по половине можно использовать.

Можно вот так повыкидывать лишние обозначения прямо в коде, можете добавить ещё, или скорректировать:
Код
Function Nejavki(r)
Dim i&, t$, k, s1$, s2$
r = r.Value
Nejavki = ""
With CreateObject("Scripting.Dictionary")
For i = 1 To UBound(r, 2)
    t = Trim(r(1, i))
    If t = "В" Then t = ""
    If t = "РП" Then t = ""
    If Len(t) Then .Item(t) = .Item(t) + 1
Next
ReDim out(1 To 2, 1 To 1)
For Each k In .keys
    s1 = s1 & "/" & .Item(k)
    s2 = s2 & "/" & k
Next
If .Count Then
    out(1, 1) = Mid(s1, 2)
    out(2, 1) = Mid(s2, 2)
    Nejavki = out
End If
End With
End Function



Цитата
macovea написал:
А можно как-нибудь искать только по тем неявкам, которые в именованном диапазоне "неявки"?
- чуть позже учту в следующей версии
Изменено: Hugo - 09.10.2024 14:16:29
 
macovea, вот версия2, с учётом указанного в именованном диапазоне "неявки"
Чтоб пересчиталось нужно изменить/воздействовать на любой аргумент, только изменение в "неявки" недостаточно, это пока не аргумент (но можно добавить)
 
вариант
Цитата
macovea:  ...как в жёлтых ячейках ...  Эксель 2013 ...
А можно как-нибудь искать только по тем неявкам, которые в именованном диапазоне "неявки"?
=ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПОДСТАВИТЬ(" "&СЧЁТЕСЛИ(G29:AL29;"О")&"  "&СЧЁТЕСЛИ(G29:AL29;"ОР")&"  "&СЧЁТЕСЛИ(G29:AL29;"Б")&"  "&СЧЁТЕСЛИ(G29:AL29;"К")&"  "&СЧЁТЕСЛИ(G29:AL29;"А")&"  "&СЧЁТЕСЛИ(G29:AL29;"НН")&" ";" 0 "; ));" ";"/")
=ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ЕСЛИ(СЧЁТЕСЛИ(G29:AL29;"О");"О ";"")&ЕСЛИ(СЧЁТЕСЛИ(G29:AL29;"ОР");"ОР ";"")&ЕСЛИ(СЧЁТЕСЛИ(G29:AL29;"Б");"Б ";"")&ЕСЛИ(СЧЁТЕСЛИ(G29:AL29;"К");"К ";"")&ЕСЛИ(СЧЁТЕСЛИ(G29:AL29;"А");"А ";"")&ЕСЛИ(СЧЁТЕСЛИ(G29:AL29;"НН");"НН ";""));" ";"/")
в новых версиях можно сцеплять значения массивов:
=ОБЪЕДИНИТЬ("/";1;ЕСЛИОШИБКА(1/(1/СЧЁТЕСЛИ(G29:AL29;неявки));""))
=ОБЪЕДИНИТЬ("/";1;ФИЛЬТР(неявки;СЧЁТЕСЛИ(G29:AL29;неявки)))
 
Цитата
написал:
версия2
Супер!!! То, что надо!
Спасибо огромное!  :D  
 
Цитата
написал:
=ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПОДСТАВИТЬ(" "&СЧЁТЕСЛИ(G29:AL29;"О")&"  "&СЧЁТЕСЛИ(G29:AL29;"ОР")&"  "&СЧЁТЕСЛИ(G29:AL29;"Б")&"  "&СЧЁТЕСЛИ(G29:AL29;"К")&"  "&СЧЁТЕСЛИ(G29:AL29;"А")&"  "&СЧЁТЕСЛИ(G29:AL29;"НН")&" ";" 0 "; ));" ";"/")=ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ЕСЛИ(СЧЁТЕСЛИ(G29:AL29;"О");"О ";"")&ЕСЛИ(СЧЁТЕСЛИ(G29:AL29;"ОР");"ОР ";"")&ЕСЛИ(СЧЁТЕСЛИ(G29:AL29;"Б");"Б ";"")&ЕСЛИ(СЧЁТЕСЛИ(G29:AL29;"К");"К ";"")&ЕСЛИ(СЧЁТЕСЛИ(G29:AL29;"А");"А ";"")&ЕСЛИ(СЧЁТЕСЛИ(G29:AL29;"НН");"НН ";""));" ";"/")
Ага, то есть, только перебором каждого значения из диапазона... Тоже вариант, да)
Тоже огромное спасибо!)) Работает так, как надо!)
 
Цитата
Hugo написал:
добавить ещё один аргумент, содержимое которого (массив данных) не учитывать
Зачем его тогда добавлять?
 
Мистер Экселистор, ну есть разные варианты -
1. выкидывать ненужное, указав в коде конкретно что не учитывать (это в листинге выше)
2. выкидывать ненужное, взяв его (это ненужное) с листа как аргумент функции
3. учитывать только то что указано в списке на листе - это в файле выше Табель_пример_v2.xlsm
4. учитывать только то что указано в списке на листе как аргумент функции - можно доработать если понадобится.
Изменено: Hugo - 09.10.2024 18:12:18
 
Hugo,
Большое спасибо. Вот как, оказывается, можно неправильно понять смысл фразы...
Страницы: 1
Наверх