Я пытаюсь определить количество новых и постоянных клиентов ежегодно.
В соответствии с приведенной выше таблицей, я перечислил новых и повторных клиентов, но этот процесс был выполнен вручную, и у меня есть много клиентов, чтобы идентифицировать таким образом.
Могу ли я узнать, есть ли какая-либо формула, чтобы я мог определить количество новых / повторных клиентов в год.
Если вы можете использовать VBA, вы можете получить решение одним нажатием кнопки. Это предлагаемое решение в основном использует формулу с очень небольшой поддержкой VBA, и это двухэтапное решение.
Поскольку вы используете сводную таблицу, делайте ваши данные в более удобном для просмотра формате, заменяя строки и столбцы в сводной таблице, т.е. пусть годы будут строками, а клиенты - столбцами.
Сохраните рабочую книгу как xlsm (рабочая книга Excel с поддержкой макросов). На листе, где находится ваша таблица, нажмите ALT + F11, чтобы открыть редактор VBA. Из меню Вставка вставьте модуль и вставьте в него следующий код. Сохраните код и рабочую книгу.
Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, ParamArray cell_ar() As Variant)
For Each cellrng In cell_ar
For Each cell In cellrng
If ignore_empty = False Then
result = result & cell & delimiter
Else
If cell <> "" Then
result = result & cell & delimiter
End If
End If
Next cell
Next cellrng
TEXTJOIN = Left(result, Len(result) - Len(delimiter))
End Function
Это функция TEXTJOIN. Это доступно в подписке на Office 365, но не доступно в других версиях. Код VBA унаследован от статьи ниже.
Источник https://www.get-digital-help.com/2016/06/07/textjoin-function/
Вам необходимо создать аналогичную таблицу, в которую должны быть добавлены цифры 1, переведенные в OLD & NEW, как показано на рисунке выше, начиная с 9-й строки в этом примере.
Формула в ячейке B9
=IF(ISBLANK(B5),"",IF(ISERROR(MATCH(B5,B4,0)),"NEW","OLD"))
Формула в ячейке B10
=IF(ISBLANK(B6),"",IF(ISERROR(MATCH(B6,B4:B5,0)),"NEW","OLD"))
Формула в клетке B11
=IF(ISBLANK(B7),"",IF(ISERROR(MATCH(B7,B4:B6,0)),"NEW","OLD"))
Теперь перетащите три формулы по всей длине таблицы. Ваши 1 не переведены на СТАРУЮ и НОВУЮ. Это Шаг1.
Создайте таблицу лет под этим и два столбца СТАРЫЙ и НОВЫЙ
Формула в B15
=CONCATENATE(COUNTIF(B9:I9,"OLD"),"(",TEXTJOIN(",",TRUE,IF(B9:I9="OLD",$B$3:$I$3,"")),")")
Нажмите CTRL+SHIFT+ENTER, чтобы создать формулу массива. Теперь вы увидите формулу в фигурных скобках и ожидаемые результаты за год в этой ячейке по мере необходимости. Перетащите эту формулу вниз до желаемого числа рассматриваемых лет.
Аналогичным образом поместите следующую формулу массива в C15
=CONCATENATE(COUNTIF(B9:I9,"NEW"),"(",TEXTJOIN(",",TRUE,IF(B9:I9="NEW",$B$3:$I$3,"")),")")
и перетащите его вниз.
Это двухступенчатый процесс. Для более лучшей версии можно выбрать полный код VBA, который может достичь этого одним нажатием кнопки.
Это решение должно работать только в этой книге из-за пользовательского кода VBA функции TEXTJOIN. Однако, если у вас есть подписка на Office 365, она должна без проблем работать в Excel 2016.
Возможно условное форматирование сделало бы работу здесь. В приведенной ниже таблице новые клиенты отображаются зеленым цветом, а повторные клиенты - синим.
Чтобы быть Новым клиентом, ячейка должна = 1, а все ячейки слева должны быть пустыми, а для повторного покупателя ячейка должна = 1, а любая из ячеек слева должна быть 1.
Таким образом, простая функция AND() может различать их.
Чтобы установить форматирование для новых клиентов, выберите C2:F10 и выберите «Условное форматирование ...» в меню "Формат". Добавьте новое правило и выберите "Классический" в качестве стиля. Выберите «Использовать формулу ...» из выпадающего списка, выберите стиль формата и введите эту формулу в поле:
=AND(SUM($B2:B2)=0,C2=1)
Для постоянных клиентов добавьте другое правило и используйте эту формулу:
=AND(SUM($B2:B2)>0,C2=1)
Примечание: процедура для этого может отличаться, в зависимости от вашей версии Excel. Ключ должен найти диалог, который позволяет вам использовать формулу для установки форматирования.
Если вам не нужно условное форматирование, вы можете использовать аналогичные формулы в другой части листа:
=IF(AND(SUM($B2:B2)=0,C2=1),"New","")
=IF(AND(SUM($B2:B2)>0,C2=1),"Repeat","")
Заполнение их вправо и вниз идентифицирует новых и повторных клиентов.
Надеюсь, это поможет, и спасибо за четкое объяснение того, что вы пытаетесь сделать. Я помню ваш вопрос некоторое время назад, и добавление небольшой таблицы, показывающей, какие клиенты являются Новыми, а какие Возвратными, сделало его намного более понятным.