Вопрос или проблема
Я пишу формулу Excel для работы. Я хотел бы сделать её немного лучше. Excel заполнен множеством информации, связанной с работой.
У меня имеется поиск по столбцу D в поисках текста (который соответствует элементу оборудования), затем он сопоставляет этот текст с тем же на листе 2 и вставляет в столбец P активен или неактивен этот текст (оборудование).
Я хотел бы разместить код в столбце Q, который читает столбец P, проверяя, написано ли там “неактивен” или “активен”.
Если написано “неактивен”, то он ищет в столбце A, к какому #P этот текст подключен.
Если посмотреть на изображение, можно увидеть, что в столбце A есть тесты, которые читают P-1, P-3. Я хочу фактически напечатать это в столбце Q рядом с неактивными, чтобы я мог быстро увидеть, на каком #P находится этот неактивный.
Я думаю, что рисунок как раз показывает, что я планирую сделать.
Если проще, то можно, чтобы код просто переходил к столбцу A и просматривал вверх, пока не найдёт значение, и просто напечатал его.
Всё нормально, если активные и неактивные имеют рядом #P, но мне нужно, чтобы это было только для неактивных. На изображении 2 показан пример окончательного продукта.
Как показано синим, это предпочтительное решение, когда текст находится только рядом с неактивными.
В основном, потому что активные не нуждаются в этом для процесса, но если проще, то вполне нормально, если как на красном, это написано на каждом.
Ответ или решение
Для решения поставленной задачи в Excel необходимо разработать специальную формулу или макрос, который будет выполнять поиск по указанным колонкам и вносить корректировки в соответствии с заданными условиями. Рассмотрим теоретическую часть, примеры и практическое применение данного решения.
Теоретическая часть
Постановка задачи
Цель состоит в том, чтобы для каждой "неактивной" записи в столбце P в колонке Q был проставлен номер, связанный с данной записью, который следует искать в столбце A. В идеале этот номер следует извлечь автоматически и указать только для неактивных элементов. В то же время, если проще оставить данную информацию и для активных записей, то это допустимо.
Решение задачи с помощью формул Excel
Excel предлагает множество функций, которые могут быть использованы для решения подобного рода задач, таких как IF, LOOKUP, INDEX, MATCH, VLOOKUP и OFFSET. Однако в нашем случае недостаточно использовать обычные поисковые функции, так как нам необходимо производить поиск по условиям со специфическими правилами.
Функция LOOKUP часто используется для поиска значений в диапазоне. Можно было бы пользоваться следующим образом: чтобы искать первое ненулевое значение выше текущей ячейки, функция LOOKUP может обратиться к массиву в обратной последовательности и вернуть первое ненулевое значение. Однако мы будем обращаться к другой колонке в зависимости от состояния текущей строки в колонке P, что делает задачу сложнее.
Пример применения
Рассмотрим конкретный пример, где таблица содержит данные о статусе оборудования в столбце P, а идентификаторы находятся в столбце A. Вы должны сканировать вверх по списку до тех пор, пока не будет найдено первое значение в столбце A, и вывести его в колонке Q, если текущая ячейка в P отмечена как "неактивный".
Примеры
Пример формулы
Допустим, наша таблица имеет следующую структуру:
| A | D | P | Q |
|---|---|---|---|
| P-1 | Хранитель | Активный | |
| P-2 | Вентиль | Неактивный | |
| Конвертер | Активный | ||
| P-3 | Преобразователь | Неактивный |
В формуле для ячейки Q2 можно было бы использовать такой синтаксис (с учётом того, что в P2 стоит "неактивный"):
=IF(P2="Неактивный", LOOKUP(2,1/(A$1:A1<>""), A$1:A1), "")Эта формула использует LOOKUP для поиска в диапазоне A от текущей строки к первой строки и возвращает последнее заполненное значение (P#) выше текущей если P в текущей строке является "неактивным".
Пример применения VBA (макрос)
Хотя формулы могут справляться с данной задачей, создание макроса VBA предоставляет больше контроля и значительно упрощает реализацию нетипичных функциональностей. Рассмотрим, как можно воспользоваться VBA для нашей задачи:
Sub UpdateInactiveStatuses()
Dim ws As Worksheet
Dim i As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
For i = 2 To ws.Cells(ws.Rows.Count, "P").End(xlUp).Row
If ws.Cells(i, "P").Value = "Неактивный" Then
ws.Cells(i, "Q").Value = FindPNumber(ws, i)
End If
Next i
End Sub
Function FindPNumber(ws As Worksheet, currentRow As Long) As String
Dim i As Long
For i = currentRow - 1 To 1 Step -1
If ws.Cells(i, "A").Value <> "" Then
FindPNumber = ws.Cells(i, "A").Value
Exit Function
End If
Next i
FindPNumber = "Not Found"
End FunctionЗдесь основной цикл в UpdateInactiveStatuses() проходит по всем файлам в колонке P. Если находиться "неактивный", вызывается функция FindPNumber, которая ищет P#, начиная с текущей строки и идя вверх по колонке A. Если найден ремонт, возвращается это значение.
Применение
Пользовательская инструкция
- Скопируйте код макроса VBA в рабочую книгу Excel (открыть Excel, нажмите ALT + F11, чтобы войти в редактор VBA, вставьте код в новом модуле).
- Выполните макрос через меню "Разработчик" > "Макросы" или назначьте горячую клавишу.
- Обновите данные и проверьте результат в колонке Q.
Данная реализация позволит автоматизировать процесс заполнения информации о статусе активный/неактивный и облегчить выявление проблем в вашем оборудовании, предоставив ясную картину состояния доставки оборудовании в вашей базе данных.