![Excel:在表中随机选择符合条件的](https://linux22.com/image/1615443/Excel%EF%BC%9A%E5%9C%A8%E8%A1%A8%E4%B8%AD%E9%9A%8F%E6%9C%BA%E9%80%89%E6%8B%A9%E7%AC%A6%E5%90%88%E6%9D%A1%E4%BB%B6%E7%9A%84.png)
我的问题如下:
我有一份参考人名单、参考人的地址和参考人所在的城市(样本如下所示)。
我的表格大约有 7000 行,引用都是唯一的,其中一些位于同一个城市并共享相同的地址。
我想知道是否可以随机选择 700 个或更多参考,但每个地址限制为 5 个参考。
我不介意有随机重复,但我不能在特定城市中拥有 700 个相同地址/街道的引用中的 55 个。
有人可以给我一些指点吗?
样本:
https://drive.google.com/file/d/1IhcVWtPqu2TiGH1xDFGBUauBm6w1OEyM/view?usp=sharing
+---------------------------+-----------------------------------+---------------+
| Reference | Address | City |
+---------------------------+-----------------------------------+---------------+
| 140120.00001.01.01.01 | EN 2-4 | ABRANTES |
+---------------------------+-----------------------------------+---------------+
| 140120.00004.01.02.01 | EN 2-4 | ABRANTES |
+---------------------------+-----------------------------------+---------------+
| 140120.00006.01.01.01 | R. NOSSA SENHORA DA CONCEIÇÃO | ABRANTES |
+---------------------------+-----------------------------------+---------------+
| 140120.00010.01.01.01 | AV. DAS FORÇAS ARMADAS | ABRANTES |
+---------------------------+-----------------------------------+---------------+
| 140120.00010.01.02.01 | AV. DAS FORÇAS ARMADAS | ABRANTES |
+---------------------------+-----------------------------------+---------------+
| 140120.00012.01.01.01 | AV. 25 DE ABRIL | ABRANTES |
+---------------------------+-----------------------------------+---------------+
| 140120.00013.01.02.01 | AV. DAS FORÇAS ARMADAS | ABRANTES |
+---------------------------+-----------------------------------+---------------+
| 140120.00015.01.01.01 | AV. 25 DE ABRIL | ABRANTES |
+---------------------------+-----------------------------------+---------------+
| 140120.00016.01.01.01 | AV. DAS FORÇAS ARMADAS | ABRANTES |
+---------------------------+-----------------------------------+---------------+
| 140120.00016.01.02.01 | AV. DAS FORÇAS ARMADAS | ABRANTES |
+---------------------------+-----------------------------------+---------------+
| 140120.00018.01.01.01 | R. SOLANO DE ABREU | ABRANTES |
+---------------------------+-----------------------------------+---------------+
| 140120.00019.01.01.01 | AV. 25 DE ABRIL | ABRANTES |
+---------------------------+-----------------------------------+---------------+
| 140120.00020.01.01.01 | AV. 25 DE ABRIL | ABRANTES |
+---------------------------+-----------------------------------+---------------+
| 140120.00025.01.01.01 | EN 3 | ABRANTES |
+---------------------------+-----------------------------------+---------------+
| 010121.00038.01.01.01 | R. JOAQUIM VALENTE DE ALMEIDA | AGUEDA |
+---------------------------+-----------------------------------+---------------+
| 010121.00038.01.02.01 | R. JOAQUIM VALENTE DE ALMEIDA | AGUEDA |
+---------------------------+-----------------------------------+---------------+
| 010121.00042.01.01.01 | R. 12 DE DEZEMBRO | AGUEDA |
+---------------------------+-----------------------------------+---------------+
| 010121.00057.01.01.01 | R. 15 DE AGOSTO | AGUEDA |
+---------------------------+-----------------------------------+---------------+
| 010121.00058.01.01.01 | R. MANUEL SOUSA CARNEIRO | AGUEDA |
+---------------------------+-----------------------------------+---------------+
| 010121.00060.01.01.01 | AV. 25 DE ABRI L | AGUEDA |
+---------------------------+-----------------------------------+---------------+
| 010121.00060.01.02.01 | AV. 25 DE ABRIL | AGUEDA |
+---------------------------+-----------------------------------+---------------+
| 010121.00061.01.01.01 | R. TEN.-COR. ALBANO MELO | AGUEDA |
+---------------------------+-----------------------------------+---------------+
| 010121.00063.01.01.01 | AV. 25 DE ABRIL | AGUEDA |
+---------------------------+-----------------------------------+---------------+
| 010121.00066.01.01.01 | AV. 25 DE ABRIL | AGUEDA |
+---------------------------+-----------------------------------+---------------+
| 010121.00069.01.01.01 | R. GUSTAVO PIMENTA | AGUEDA |
+---------------------------+-----------------------------------+---------------+
| 010121.00071.01.01.01 | R. JOSÉ DE SUCENA | AGUEDA |
+---------------------------+-----------------------------------+---------------+
| 010124.00062.01.02.01 | R. CABEDO E LENCASTRE | AGUEDA |
+---------------------------+-----------------------------------+---------------+
| 000150.11559.01.01.01 | PQ. DE ESTACIONAMENTO | ALGARVE SHOPPING |
+---------------------------+-----------------------------------+---------------+
| 080106.00085.01.02.01 | AV. DOS DESCOBRIMENTOS | ALBUFEIRA |
+---------------------------+-----------------------------------+---------------+
| 080106.00089.01.01.01 | AV. DOS DESCOBRIMENTOS | ALBUFEIRA |
+---------------------------+-----------------------------------+---------------+
| 080106.00089.01.02.01 | AV. DOS DESCOBRIMENTOS | ALBUFEIRA |
+---------------------------+-----------------------------------+---------------+
| 080106.00091.01.02.01 | AV. DOS DESCOBRIMENTOS | ALBUFEIRA |
+---------------------------+-----------------------------------+---------------+
| 080106.00092.01.01.01 | AV. DOS DESCOBRIMENTOS | ALBUFEIRA |
+---------------------------+-----------------------------------+---------------+
| 080106.00092.01.02.01 | AV. DOS DESCOBRIMENTOS | ALBUFEIRA |
+---------------------------+-----------------------------------+---------------+
从 35 个样本中最终选定 17 个参考文献的示例:
在这个 34 行的示例中,我可能想要 20 个引用,我不介意有 2 个重复,但 20 个中有 5 个“AV. DOS DESCOBRIMENTOS”,这就是一个问题。因此,在这种情况下,我希望每个城市最多有 2 个相同的地址
+---------------------------+-----------------------------------+---------------+
| Reference | Address | City |
+---------------------------+-----------------------------------+---------------+
| 140120.00001.01.01.01 | EN 2-4 | ABRANTES |
+---------------------------+-----------------------------------+---------------+
| 140120.00006.01.01.01 | R. NOSSA SENHORA DA CONCEIÇÃO | ABRANTES |
+---------------------------+-----------------------------------+---------------+
| 140120.00010.01.01.01 | AV. DAS FORÇAS ARMADAS | ABRANTES |
+---------------------------+-----------------------------------+---------------+
| 140120.00012.01.01.01 | AV. 25 DE ABRIL | ABRANTES |
+---------------------------+-----------------------------------+---------------+
| 140120.00018.01.01.01 | R. SOLANO DE ABREU | ABRANTES |
+---------------------------+-----------------------------------+---------------+
| 140120.00025.01.01.01 | EN 3 | ABRANTES |
+---------------------------+-----------------------------------+---------------+
| 010121.00038.01.02.01 | R. JOAQUIM VALENTE DE ALMEIDA | AGUEDA |
+---------------------------+-----------------------------------+---------------+
| 010121.00042.01.01.01 | R. 12 DE DEZEMBRO | AGUEDA |
+---------------------------+-----------------------------------+---------------+
| 010121.00057.01.01.01 | R. 15 DE AGOSTO | AGUEDA |
+---------------------------+-----------------------------------+---------------+
| 010121.00058.01.01.01 | R. MANUEL SOUSA CARNEIRO | AGUEDA |
+---------------------------+-----------------------------------+---------------+
| 010121.00060.01.01.01 | AV. 25 DE ABRIL | AGUEDA |
+---------------------------+-----------------------------------+---------------+
| 010121.00069.01.01.01 | R. GUSTAVO PIMENTA | AGUEDA |
+---------------------------+-----------------------------------+---------------+
| 010121.00071.01.01.01 | R. JOSÉ DE SUCENA | AGUEDA |
+---------------------------+-----------------------------------+---------------+
| 010124.00062.01.02.01 | R. CABEDO E LENCASTRE | AGUEDA |
+---------------------------+-----------------------------------+---------------+
| 000150.11559.01.01.01 | PQ. DE ESTACIONAMENTO | ALGARVE |
+---------------------------+-----------------------------------+---------------+
| 080106.00085.01.02.01 | AV. DOS DESCOBRIMENTOS | ALBUFEIRA |
+---------------------------+-----------------------------------+---------------+
| 080106.00089.01.01.01 | AV. DOS DESCOBRIMENTOS | ALBUFEIRA |
+---------------------------+-----------------------------------+---------------+