Excel: показать список значений на основе запроса в диапазоне

388
Haaid

У меня есть такая таблица:

a | tomato b | pear c | tomato d | pear

Я хотел бы создать формулу, которая позволяет мне выбирать, какие строки содержат помидор, а какие - грушу. таким образом, результатом формулы будет «a, c» для запроса помидора и «b, d» для запроса помидора груши.

это можно сделать в Excel (и если да, то как?)

Спасибо!

1
Не существует функции рабочего листа, которая (условно) объединяет строковые значения ячейки диапазона в одно значение. Вы можете решить задачу, используя дополнительные столбцы или определяемые пользователем функции (код VBA - самое простое решение, я думаю). Akina 5 лет назад 0
Привет Акина, спасибо за твой ответ. Можете ли вы объяснить, что вы подразумеваете под «использованием дополнительных столбцов»? Потому что, если бы я мог просто получить возврат, такой как | c в двух разных столбцах, затем я замерз, после чего конкатенирую эти клетки, чтобы получить требуемый результат. Haaid 5 лет назад 0
@ Акина Вам не нужна функция, которая условно объединяет. Вы можете сгенерировать соответствующие значения с помощью функции массива, а затем использовать `CONCAT ()` или `TEXTJOIN ()`. Никаких дополнительных столбцов или VBA не требуется. (Если, конечно, у вас нет Excel 2016) robinCTS 5 лет назад 0
* Если у вас нет Excel 2016, конечно. * Увы, только 2010 :( ... Akina 5 лет назад 0
@Akina Просто используйте мою поставляемую поли-заливку UDF ;-) (Или вы можете искать другие версии в Интернете.) Кстати, я только 2007 :-P robinCTS 5 лет назад 0

2 ответа на вопрос

1
robinCTS

Это может быть выполнено без VBA с помощью TEXTJOIN()функции, представленной в Excel 2016. Если у вас нет этой версии Excel, вы можете установить UDF с поли-заполнением. Я поставил основной в конце этого ответа.

Worksheet Screenshot

Array-введите следующую формулу в E2:

{=TEXTJOIN(", ",TRUE,IFERROR(INDEX(A1:A5,N(IF(1,SMALL(IFERROR(1/(1/((B1:B5=D2)*ROW(B1:B5))),FALSE),ROW(INDEX(E:E,1):INDEX(E:E,ROWS(B1:B5))))))),""))}

Предварительно подтвержденная формула выглядит следующим образом:

{= TEXTJOIN( ", ", TRUE, IFERROR( INDEX( A1:A5, N(IF(1, SMALL( IFERROR(1/(1/((B1:B5=D2)*ROW(B1:B5))),FALSE), ROW(INDEX(E:E,1):INDEX(E:E,ROWS(B1:B5))) ) )) ), "" ) )}

Заметки:

  • Предварительно подтвержденная формула действительно работает, если введена.


Моя версия TEXTJOIN()поли-заполнения UDF:

'============================================================================================ ' Module : <any standard module> ' Version : 0.1.1 ' Part : 1 of 1 ' References : Optional - Microsoft VBScript Regular Expressions 5.5 [VBScript_RegExp_55] ' Source : https://superuser.com/a/1331555/763880 '============================================================================================ Public Function TEXTJOIN( _ ByRef delimiter As String, _ ByRef ignore_empty As Boolean, _ ByRef text1 As Variant _ ) _ As String Dim ƒ As Excel.WorksheetFunction: Set ƒ = Excel.WorksheetFunction  Const DELIMITER_ As String = "#" Const PATTERN_ As String = "^(?:#)+|(?:#)+$|(#)"  Static rexDelimiterEscaper As Object ' VBScript_RegExp_55.RegExp ' ## Object Static rexEmptyIgnorer As Object ' VBScript_RegExp_55.RegExp ' ## Object If rexEmptyIgnorer Is Nothing _ Then Set rexEmptyIgnorer = CreateObject("VBScript.RegExp") ' New VBScript_RegExp_55.RegExp ' ## CreateObject("VBScript.RegExp") With rexEmptyIgnorer .Global = True .Pattern = PATTERN_ ' Replacement = "$1" End With Set rexDelimiterEscaper = CreateObject("VBScript.RegExp") ' New VBScript_RegExp_55.RegExp ' ## CreateObject("VBScript.RegExp") With rexDelimiterEscaper .Global = True .Pattern = "(.)" ' Replacement = "\$1" End With End If  Dim varText1 As Variant Select Case TypeName(text1) Case "Range": varText1 = ƒ.Transpose(text1.Value2) If text1.Rows.Count = 1 Then varText1 = ƒ.Transpose(varText1) If text1.Columns.Count = 1 Then varText1 = Array(varText1) End If Case "Variant()": On Error Resume Next If LBound(text1, 2) <> LBound(text1, 2) Then varText1 = text1 Else varText1 = ƒ.Transpose(text1) End If On Error GoTo 0 Case Else: varText1 = Array(text1) End Select If ignore_empty _ Then With rexEmptyIgnorer .Pattern = Replace(PATTERN_, DELIMITER_, rexDelimiterEscaper.Replace(delimiter, "\$1")) TEXTJOIN = .Replace(Join(varText1, delimiter), "$1") End With Else TEXTJOIN = Join(varText1, delimiter) End If  End Function

Заметки:

  • Это не правильный поли-наполнитель:
    • Первые два аргумента не являются обязательными;
    • Если вы не хотите использовать разделитель, вы должны передать пустую строку в качестве первого параметра.
    • Допустим только один другой (также обязательный) аргумент.
  • Для третьего аргумента вы можете передать что угодно, кроме многомерного массива / диапазона. Это приведет к #VALUE!ошибке.
  • Он должен быть очень быстрым, особенно для больших входов, поскольку он не использует петли. Если вы не игнорируете пустые значения, это будет молниеносно. Игнорирование их будет медленнее, так как необходимо использовать пару регулярных выражений и дополнительные манипуляции со строками.
0
Akina

Пользовательская функция может помочь.

Представьте, что у вас есть следующие данные на листе:

 | A | B --+-----+--------- 1 | a | tomato 2 | b | pear 3 | c | tomato 4 | d | pear

Откройте редактор VBA (Alt-F11). Вставьте новый модуль (Вставка - Модуль). Вставьте код ниже в это:

Public Function GetValues(rngSource As Range, strValue As String) As String Dim i As Integer If rngSource.Columns.Count <> 2 Then GetValues = "#ERROR - Source range must have 2 columns!" Exit Function End If For i = 1 To rngSource.Rows.Count If rngSource.Cells(i, 2) = strValue Then GetValues = GetValues & "," & rngSource.Cells(i, 1) End If Next GetValues = Mid(GetValues, 2) End Function

Закройте редактор VBA.

Перейти к рабочему листу. Вставьте значение «помидор» в ячейку D1.

Выберите ячейку E1. Вставьте в него следующую формулу:

=GetValues(A1:B4;D1)

Значение ячейки E1 - «a, c». Измените значение D1 на «груша» - значение E1 изменится. Измените значение D1 на «яблоко» - значение E1 изменится на пустую строку.

Похожие вопросы