Excel/GSheets在动态列表中计算唯一值与重复出现的值 [英] Excel/GSheets count unique versus reoccuring values in dynamic list

问题描述

因此，我有一个大型的网络研讨会会议参与者数据集-大约3万多行.我需要编写一个函数(在Excel或Google表格中)，以确定每个参与者是第一次还是返回参与者.数据集按事件的时间顺序从最早到最新.让我们使用下面的简化版本在此处更好地说明业务案例.

So I have a large data set of webinar conference attendees - about 30k+ rows. I need to write a function (either in Excel or Google Sheets) to determine if each participant is a first time or returning attendee. The data set is sorted chronologically by event from oldest to most recent. Let's use the simplified version below to better illustrate the business case here.

在此处签出简单样本集.

我考虑为每个网络研讨会集使用命名范围，并搜索用户是否存在于该范围内，但考虑到规模和各种网络研讨会参与者的规模，这会很困难.我还考虑过对各种参与者进行索引匹配，但是因为每个事件的参与者数量变化很大(从5到500)，因此也很琐碎.我觉得这里可能缺少相对简单的解决方案-请协助！谢谢！

I considered using named ranges for each of the webinar sets and searching to see if the user exists in that range but given the scale and various webinar attendee sizes that would be tough. I also thought about index-matching the various attendees but because the number of attendees per event is so variable (anywhere from 5 to 500) that too would be frivolous. I feel like I might be missing a relatively simple solution here - please assist! Thanks!

推荐答案

尝试以下公式.它将在excel和google sheet上都可以使用.

Have a try on below formula. It will work both on excel and google sheet.

=IF(COUNTIF($B$2:$B2,B2)>1,"Returning","New")

如果您需要在第一个Web内部显示 N/A ，然后使用以下公式.

If you need to show N/A for first web inner then use below formula.

=IF(A2="A","N/A",IF(COUNTIF($B$2:$B2,B2)>1,"Returning","New"))

这篇关于Excel/GSheets在动态列表中计算唯一值与重复出现的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！