Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
формула с ВПР или аналог для выборки значения по периодам времени, формула с ВПР или аналог для выборки значения по периодам времени
 
#1
06.05.2026 15:19:16
Здравствуйте, прошу помощи

дано две таблицы,
в одной указаны периоды в которые работник работал на разных должностях,

ДАНО
Иванов И.И. с 01.03.2011 по 31.12.2011 управляющий
Иванов И.И. с 01.01.2012 по 01.12.2018 директор

в другую нужно перенести должность этого работника в произвольные периоды.

ЗАПОЛНИТЬ
Иванов И.И. с 14.03.2011 по 31.03.2011  - должность?
Иванов И.И. с 01.04.2011 по 30.04.2011  - должность?
Иванов И.И. с 03.05.2011 по 19.05.2011  - должность?
Иванов И.И. с 20.05.2011 по 31.12.2011  - должность?
Иванов И.И. с 01.01.2012 по 24.04.2013 - должность?

две большие таблицы с разными фамилиями и периодами

Помогите пожалуйста с формулой
Изменено: vopona - 06.05.2026 15:32:26
 
 
 
#2
06.05.2026 15:34:28
vopona, добрый день. Формула массива :
=ИНДЕКС(Таблица1[должность];ПОИСКПОЗ(1;(Таблица1[Фамилия И.О.]=[@[Фамилия И.О.]])*(Таблица1[ОТ]<=[@С])*(Таблица1[ДО]>=[@ПО]);0))
 
 
 
#3
06.05.2026 15:38:29
Код
=ИНДЕКС($D$1:$D$4;МАКС((A12=$A$3:$A$4)*(B12>=$B$3:$B$4)*(C12<=$C$3:$C$4)*СТРОКА($A$3:$A$4)))
Формула массива. Ctrl+Shift+Enter.
 
 
 
#4
06.05.2026 15:38:35
Ещё вариант, без массивного ввода.
Код
=ПРОСМОТР(1;(Таблица1[ОТ]<=[@С])*(Таблица1[ДО]>=[@ПО])*(Таблица1[Фамилия И.О.]=[@[Фамилия И.О.]]);Таблица1[должность])
Изменено: gling - 06.05.2026 15:40:54
 
 
 
#5
06.05.2026 15:42:03
Alex, МатросНаЗебре, gling, БОЛЬШУЩЕЕ всем спасибо
 
 
 
#6
06.05.2026 16:27:11
Вариант макросом.
Код
Sub Должности()
    Dim tbSource As ListObject
    Set tbSource = ActiveSheet.ListObjects("Таблица1")

    Dim tbTarget As ListObject
    Set tbTarget = ActiveSheet.ListObjects("Таблица24")

    Dim aSour As Variant
    ReDim aSour(1 To 4)
    aSour(1) = tbSource.ListColumns("Фамилия И.О.").DataBodyRange.Value
    aSour(2) = tbSource.ListColumns("ОТ").DataBodyRange.Value
    aSour(3) = tbSource.ListColumns("ДО").DataBodyRange.Value
    aSour(4) = tbSource.ListColumns("должность").DataBodyRange.Value
    
    Dim rSour As Range
    Set rSour = tbSource.ListColumns("должность").DataBodyRange
    
    Dim rTarg As Range
    Set rTarg = tbTarget.ListColumns("должность").DataBodyRange
    rTarg.Cells.ClearContents
    
    Dim aTarg As Variant
    ReDim aTarg(1 To 4)
    aTarg(1) = tbTarget.ListColumns("Фамилия И.О.").DataBodyRange.Value
    aTarg(2) = tbTarget.ListColumns("С").DataBodyRange.Value
    aTarg(3) = tbTarget.ListColumns("ПО").DataBodyRange.Value
    aTarg(4) = tbTarget.ListColumns("должность").DataBodyRange.Value
    
    
    Dim fioY As Object
    Set fioY = CreateObject("Scripting.Dictionary")
    Dim ys As Variant
    For ys = 1 To UBound(aSour(1), 1)
        fioY(aSour(1)(ys, 1)) = fioY(aSour(1)(ys, 1)) & ys & " "
    Next
    
    Dim dtMin As Date, dtMax As Date
    Dim yt As Long, vy As Variant
    For yt = 1 To UBound(aTarg(1), 1)
        
        If fioY.Exists(aTarg(1)(yt, 1)) Then
            For Each vy In Split(fioY(aTarg(1)(yt, 1)), " ")
                If vy <> "" Then
                    ys = CLng(vy)
                    dtMin = WorksheetFunction.Max(aTarg(2)(yt, 1), aSour(2)(ys, 1))
                    dtMax = WorksheetFunction.Min(aTarg(3)(yt, 1), aSour(3)(ys, 1))
                    If dtMin <= dtMax Then
                        aTarg(4)(yt, 1) = aTarg(4)(yt, 1) & rSour.Cells(ys, 1).Address(1, 1, xlA1) & " "
                    End If
                End If
            Next
            
            aTarg(4)(yt, 1) = Trim(aTarg(4)(yt, 1))
            aTarg(4)(yt, 1) = Replace(aTarg(4)(yt, 1), " ", "&"",""&")
            aTarg(4)(yt, 1) = "=" & aTarg(4)(yt, 1)
            rTarg.Cells(yt, 1).Value = aTarg(4)(yt, 1)
        End If
    Next
    
'    rTarg.Value = aTarg(4)
End Sub
 
 
 
#7
06.05.2026 20:25:21
вариант
=ВПР(B12;Таблица1[[ОТ]:[должность]];3;1)

(на показанном примере работает, надо проверять на более расширенном, так как показан только Иванов, то ф-ла без привязки к ФИО)
Изменено: ВовавВова - 06.05.2026 20:27:23
познакомился с Excel
 
 
 
#8
07.05.2026 07:53:23
ВовавВова, спасибо,
но у меня две большие таблицы с разными фамилиями и периодами.
 
 
 
#9
07.05.2026 09:13:02
Цитата
написал:
но у меня две большие таблицы с разными фамилиями и периодами.
в D12 ф-ла массива
=ЕСЛИ(
ЕЧИСЛО(ПОИСКПОЗ(1;(Таблица1[ОТ]<=B12)*(Таблица1[ДО]>=C12)*(Таблица1[Фамилия И.О.]=A12);0));
ИНДЕКС(Таблица1[должность];ПОИСКПОЗ(1;(Таблица1[ОТ]<=B12)*(Таблица1[ДО]>=C12)*(Таблица1[Фамилия И.О.]=A12);0);1);"no")

(надо тестировать на большой таблице)
познакомился с Excel
 
 
 
#10
07.05.2026 09:31:55
ВовавВова, спасибо большое,
какие вы все тут молодцы,
отзывчивые и очень умные.
Без иронии. Правда.
 
 
 
#11
07.05.2026 13:15:32
по результату тестирования всех вариантов предложенных тут,
к моей большой таблице удалось применить только один вариант, который заработал.

вот этот #3 персональное спасибо МатросНаЗебре,
Код
=ИНДЕКС($D$1:$D$4;МАКС((A12=$A$3:$A$4)*(B12>=$B$3:$B$4)*(C12<=$C$3:$C$4)*СТРОКА($A$3:$A$4)))

остальные варианты не заработали возможно потому, что у меня руки как то не так растут, или я как то не так задачу описала
ВСЕМ ещё раз огроменное спасибо

****
дополняю, да, исходные данные надо было доработать, и все формулы заработали.
Изменено: vopona - 07.05.2026 14:30:32
 
 
 
#12
07.05.2026 17:42:21
vopona, там может быть такое, что периоды будут/могут включать две должности? Вот как например работает макрос, но там в результате косяк возможно потому что ставит ссылки в умную таблицу...
макросом на словаре можно реализовать ещё так - сперва собрать в словаре данные по каждой персоне в каждый день, затем в цикле по второй таблице это всё извлечь каждому.
Вы дайте пример чуть поразнообразнее, и если есть пересечения должностей то и это тоже нужно показать.
 
 
 
#13
07.05.2026 18:39:26
Цитата
написал:
ВСЕМ ещё раз огроменное спасибо
вот файл с альтернативной формулой (ф-ла массива)
добавлен новый ФИО для большего разнообразия
познакомился с Excel
 
 
 
#14
07.05.2026 19:17:04
Цитата
vopona написал:
ВСЕМ ещё раз огроменное спасибо
- вот макрос для всех примеров с вариантом если вдруг должности в итоге должны собраться не уникальные
код
 
 
 
#15
08.05.2026 09:00:48
ВовавВова, Hugo, спасибо большое, все формулы рабочие,
просто я изначально немного не правильно с исходными данными работала.

Я задачу уже решила.
Ещё раз убеждаюсь, какие здесь отзывчивые и профессиональные форумчане.
Изменено: vopona - 08.05.2026 09:01:54
 
 
 
#16
08.05.2026 09:13:02
Цитата
написал:
все формулы рабочие
Рад помочь.
Не оставляйте без внимания и другие предложенные варианты от Hugo, Матроса и gling.
Вполне возможно, что они вам тоже пригодятся.
познакомился с Excel
 
 
 
#17
08.05.2026 09:45:28
Если прям по теме
Цитата
формула с ВПР
такой вариант:
=ЕСЛИОШИБКА(ВПР(A12;ЕСЛИ((Таблица1[Фамилия И.О.]=A12)*НЕ((Таблица1[ОТ]>C12)+(Таблица1[ДО]<B12));Таблица1);4;);"не найдено")
но если
Цитата
может быть такое, что периоды будут/могут включать две должности? [или больше]
то "выдернет" первую должность
этот вариант:
=ЕСЛИОШИБКА(ПРОСМОТР(;-1/(Таблица1[Фамилия И.О.]=A12)/НЕ((Таблица1[ОТ]>C12)+(Таблица1[ДО]<B12));Таблица1[должность]);"не найдено")
"выдернет" последнюю должность
можно "выдернуть" все должности):
=ОБЪЕДИНИТЬ(", ";1;ФИЛЬТР(Таблица1[должность];(Таблица1[Фамилия И.О.]=A12)*НЕ((Таблица1[ОТ]>C12)+(Таблица1[ДО]<B12));"не найдено"))
...если есть соответствующие функции
 
 
 
#18
08.05.2026 09:56:26
ПавелW, спасибо большое,
отличное решение
 
 
 
Страницы: 1
Читают тему
Наверх