A列 B列 C列 D列 E列
6 6 2 0 5
7 0 1 1 8
5 7 8 2 0
2 6 4 6 2
7 0 9 8 6
1 7 5 8 5
2 2 3 2 0
9 6 8 1 5
3 7 1 8 4
6 3 3 1 3
0 1 4 0 7
3 5 0 1 3
6 9 2 1 6
用excel函数从这五列中随机选取五个数字出来,并且,如果第一行选取了A列的数字,那么第一行的其他列的数字不可以再选取,在A列中如果选取了第三行的数字,那么A列中其他行的数字也不能再选取,用函数怎么写呢?谢谢各位高手
ä¸æ¯å¾æç½é¢ä¸»çè¦æ±ï¼ä¸»è¦æ¯å¯¹ä»ä¹æ
åµä¸è½å没ç解éãæ¯ä¸æ¯ä»»æè¡ä»»æåé½åªè½åä¸æ¬¡ï¼å°±æ¯5个æ°å¿
é¡»5åä¸ååä¸ä¸ªï¼ä¸åä¸åè¡ï¼
å¦ææ¯ï¼ä»ç¶æä¸ä¸ªå¦ä½ç¡®å®æ¯è¾¾å°äºç®ççé®é¢ï¼å 为æç»çæ°æ®åå¨å¤§ééå¤æ°æ®ï¼å³ä½¿åèªä¸åçè¡ååï¼åçç»æï¼ä¹æ æ³ç¡®å®æ¯å¦æ¯æè¦æ±çåçæ°ãæ以ä¸é¢ç解çä¸ï¼å å¨G1:K1ä¸ç¨å ¬å¼ç¡®å®ä»åªäºåå æ ¼åæ°ï¼å ¬å¼çç»æç¨R1C1å¼ç¨æ¹å¼ï¼å³Råçæ°å表示第å è¡ï¼Cåé¢çæ°å表示第å åï¼
å 为éæºåæ°ï¼è¦è¾¾å°ä¸é¢çç®çï¼å°±è¦åæ°ç¡®å®ï¼ä¸åååãæ以è¦å å¨é项çå ¬å¼é项å¡ä¸å¾éâå¯ç¨è¿ä»£è®¡ç®âåç¡®å®ï¼ä¸å 为循ç¯è®¡ç®æ¬¡æ°è¾å¤ï¼è¦æ循ç¯æ¬¡æ°è®¾ç½®å¾å¤§ä¸äºï¼å¦10000ã主è¦æ¯å 为æ£å¥½5åï¼æ¯åé½è¦åå°ä¸ä¸éå¤ï¼å¯¼è´éæºå¾ªç¯è®¡ç®é大ã
ç¡®å®åæ°çåå
æ ¼ä½ç½®ï¼
G1=IF(OR(G1={"",0}),ADDRESS(RANDBETWEEN(1,13),RANDBETWEEN(1,5),1,0),G1)
H1=IF(OR((H1={"",0}),RIGHT(H1)=RIGHT($G1:G1),LEFT(H1,3)=LEFT($G1:G1,3)),ADDRESS(RANDBETWEEN(1,13),RANDBETWEEN(1,5),1,0),H1)
åæ¶æCtrl+Shift+Enterä¸é®è¾å ¥æ°ç»å ¬å¼ï¼å³æå°K1
è§å¯æ¯å¦è¿æéå¤çè¡å·æå顺åºå·ï¼å¦æï¼æF9ï¼ç´å°æ²¡æéå¤ååæF9ä¹ä¸ä¼åçååãè¿æ ·å°±å¨G1:K1ä¸ç¡®å®è¦å¼ç¨æ°æ®çåå æ ¼ä½ç½®ãä¸è¿äºä½ç½®æ¢æ¯éæºç¡®å®çï¼åä¿è¯æ²¡æéå¤çè¡ååã
3. å¨G2:K2ä¸ä»A1:E13ä¸å¼ç¨G1:K1ç¡®å®çå¼ç¨ä½ç½®çæ°æ®ï¼
G2=INDIRECT(G1,)
å³æå°K2
å¾ä¸æ°æ®æ¯å¤å¶çé¢ä¸»æé®ä¸çæ°æ®ã
追çä¸ç¨R1C1å¼ç¨ä¹æ¯å¯ä»¥çï¼åæ¥èèè¦å¤æè¡ç顺åºå·ååç顺åºå·ä¸è½ç¸çï¼å°±èèç¨R1C1å¼ç¨ï¼è¡ååé½ç¨æ°åæ¥è¡¨ç¤ºï¼ä»¥ç¡®å®æ¯å¦ä¸åé¢çè¡å·æåå·æç¸ççãå ¶å®ï¼ææ¬ä¸æ ·å¯ä»¥å¤ææ¯å¦ç¸çï¼æ以å¯ä»¥ä¸ç¨R1C1å¼ç¨ï¼
G1=IF(OR(G1={"",0}),ADDRESS(RANDBETWEEN(1,13),RANDBETWEEN(1,5),4),G1)
H1==IF(OR((H1={"",0}),MID(H1,2,2)=MID($G1:G1,2,2),LEFT(H1)=LEFT($G1:G1)),ADDRESS(RANDBETWEEN(1,13),RANDBETWEEN(1,5),4),H1)
åæ¶æCtrl+Shift+Enterä¸é®è¾å ¥æ°ç»å ¬å¼ï¼å³æå°K1
G2=INDIRECT(G1)
å³æå°K2
å¨F1æ¯ç©ºç½åå æ ¼çåæä¸ï¼ä¹å¯ä»¥åªç¨çä¸ä¸ªå ¬å¼ï¼
G1=IF(OR((G1={"",0}),MID(G1,2,2)=MID($F1:F1,2,2),LEFT(G1)=LEFT($F1:F1)),ADDRESS(RANDBETWEEN(1,13),RANDBETWEEN(1,5),4),G1)
åæ¶æCtrl+Shift+Enterä¸é®è¾å ¥æ°ç»å ¬å¼ï¼å³æå°K1
兄弟啊,你这样就把A列中重复的数字去掉了,但是我不想去掉,我是想随机选取单元格,而且选取的单元格不重叠