
Googleã¹ãã¬ããã·ãŒã ãã«ããŠã³ãªã¹ã掻çšè¡ 4ïŒããŒãã« + æ°åŒã§é£åãã«ããŠã³ïŒ
ååã®ç¶ãã®GASåã§ã¯ãªãã話ã¯å€ãããŸãããã«ããŠã³ãªã¹ã ã·ãªãŒãºãæžããããšæããŸãã
ãã«ããŠã³ãªã¹ãã çªå€ç·šã®ãè€æ°éžæãã«ããŠã³ããå ¥ãããš ä»åã§5æ¬ç®ããšããããã§ãã¬ãžã³ã«ãŸãšããŸããã
ååã®note㯠Googleã¹ãã¬ããã·ãŒãã®ç»åãä¿åããæ¹æ³ã®å®å šçã£ãŠããšã§ãã»ã«äžç»åãã»ã«å ç»åãäžæ¬ä¿åãããã¯ããã¯ã«ã€ããŠãŸãšããŸããã
ããŒãã«æ©èœ + æ°åŒ ã§é£åãã«ããŠã³ãå®çŸãããïŒ
ä»åã®ãŽãŒã«ã¯ Googleã¹ãã¬ããã·ãŒãã§ã¯ãªããªãé£ããé£åãã«ããŠã³ã®äœæã§ãã
åºæ¬ã®2æ®µéæ¢ãŸãã§ã¯ãªã 3段é以äžã®ãã«ããŠã³ãç°¡åã«äœãæ¹æ³ã玹ä»ããŸãã
é£åãã«ããŠã³ã£ãŠãªã«ïŒã£ãŠäººã¯ãå ã« ãã«ããŠã³ã·ãªãŒãºã®ç¬¬2åãé£åãã«ããŠã³ã®åºæ¬ãããèªã¿ãã ããã
ãšããããäœãããã®ã¯ âãããªãã€ã§ãã

ãããïŒ3é£ãã«ããŠã³ã£ãŠ ãã«ããŠã³ã·ãªãŒãºã®ç¬¬3åã§ãã£ãŠãªãã£ãã£ãïŒããšæ°ã¥ããå¥ç¹ãªäººã¯ãçžåœãªèªè ã§ãããããããšãããããŸãã
3é£ãã«ããŠã³äœææ¹æ³ã¯Â ãGoogleã¹ãã¬ããã·ãŒã ãã«ããŠã³ãªã¹ã掻çšè¡3 ïŒé£åãã«ããŠã³å¿çšïŒãã®åã§äžåºŠç޹ä»ããŠããŸãã
ãã㯠ãã«ããŠã³ãæäœããã·ãŒãã«åå¥ã®äœæ¥é åãäœããã1ã€ã®ãã¹ã¿ãš1ã€ã®åŒã§å®çŸãã3é£ä»¥äžã«æ¡åŒµå¯èœãªé£åãã«ããŠã³ã§ãèªåãšããŠã¯å²ãšæ°ã«å ¥ã£ãŠããã§ããã
ã»åŒãèšå®ãè€éã§ãããããããïŒã€ãŸãé¢åïŒïŒ
ã»è€æ°ã®ã·ãŒãã§é£åãã«ããŠã³ãå®çŸããã®ãé£ãã
ã»åæã«è€æ°äººãæäœããã±ãŒã¹ã«å¯Ÿå¿ã§ããªã
ãšããæ¬ ç¹ããããŸããã
ããããè§£æ¶ããã®ããä»å玹ä»ãã ãããŒãã«æ©èœ ïŒ æ°åŒãã§å®çŸããé£åãã«ããŠã³ã§ãã
以åã®æ¹æ³ã«æ¯ã¹ããšãã£ãšç°¡åã§ã1ã€ã®ãã¹ã¿ãš1ã€ã®åŒãããã° é£åãã«ããŠã³ãå®çŸã§ããŸããããã®ã·ãŒããã³ããŒããŠå©çšããããšãå¯èœã§ãã
ãã ãããã«ããŠã³ãªã¹ããšåãè¡ã« éžæè¢ãšããŠåç §ãããçºã®äœæ¥é åãå¿ èŠãšãªããŸãã

åãé衚瀺ãšããã°åé¡ãªããšæããŸãããã©ãããŠãåãã·ãŒãå ã«äœæ¥é åãäœããªãïŒïŒäœããããªãïŒïŒãšããå Žåã¯ã察å¿ã§ããŸããã®ã§ãäºæ¿ãã ããã
ããã§ã¯äœãæ¹ãèŠãŠãããŸãããïŒ
Step1. éžæè¢ã®ãã¹ã¿ããŒãã«ãäœã

第3åã®æãšåããã¹ã¿ã䜿ã£ãŠ3é£ãã«ããŠã³ãäœã£ãŠã¿ãŸãããã
ãŸãã¯å ã«ãªããã¹ã¿ããŒãã«ãäœããŸãã
ããã¯äžã®ç»åã®ãããªãªã¹ã圢åŒãšããå¿ èŠããããŸãã
ãµã³ãã«ããŒã¿ã¯ã³ãã© â ïŒã³ããããŠå©çšïŒ
é
ç®1 é
ç®2 é
ç®3
èé¡ è±è ããŒã¯ãœããŒ
èé¡ è±è ãã³ã«ã
èé¡ è±è ããããçŒã
èé¡ é¶è ããã³ãœããŒ
èé¡ é¶è åæã
èé¡ é¶è ããã³ã«ã
èé¡ çè ããçŒã
èé¡ çè ããã¶ããã¶
èé¡ çè ã¹ããŒã
éè ãã£ãã ååã
éè ãã£ãã ããŒã«ãã£ãã
éè ãã£ãã éèçã
éè ãã³ãžã³ ã°ã©ãã»
éè ãã³ãžã³ ã·ãªã·ãª
éè ãã³ãžã³ ãã ã«
éè ã¿ããã® ã¹ãŒã
éè ã¿ããã® ããªã
éè ãžã£ã¬ã€ã¢ èããã
éè ãžã£ã¬ã€ã¢ ããããã¿ãŒ
éè ãžã£ã¬ã€ã¢ ããããã©ã€
æç© ãªã³ãŽ ã¢ããã«ãã€
æç© ãªã³ãŽ ãªã³ãŽãžã£ã
æç© ããã ã¯ã¬ãŒã
æç© ããã ã¹ã ãŒãžãŒ
æç© ãã«ã³ ãžã¥ãŒã¹
æç© ãã«ã³ ãŒãªãŒ
æç© ã€ã㎠ã€ããŽãžã£ã
æç© ã€ã㎠倧çŠ
æç© ã€ã㎠ã·ã§ãŒãã±ãŒããã®ããŒã¿ã Googleã¹ãã¬ããã·ãŒãã®æ°æ©èœ ã§ ããŒãã«å

ããŒãã«åã¯ããã¹ã¿ããšããŠãããŸãããã

ããŒãã«ã«ããå©ç¹ã¯ããŒã¿ç¯å²ã ããŒãã«åã§åŒã³åºããïŒåç §ã§ããïŒããšã§ãã

èŠåºããé€ãããŒã¿éšåãäžžããšåŒã³åºãããå Žåã¯
=ARRAYFORMULA(ãã¹ã¿)
ãããªåŒã§å®çŸã§ããŸããã·ãŒãåãæ°ã«ããå¿ èŠããªãã®ã§äŸ¿å©ã§ããã
ããŒãã«æ©èœã«ã€ããŠè©³ããç¥ãããæ¹ã¯ãnoteã«ãŸãšããŠããŸãã®ã§ãã¡ããåèã«ããŠãã ããã
Step2. å¥ã·ãŒãã§1段ç®ã®ãã«ããŠã³ãäœã

次ã«å¥ã·ãŒãã«ãã«ããŠã³ãçšæããŸãããã
1è¡ç®ãèŠåºãè¡ãšããŠã2è¡å以éã®AïœCåããã«ããŠã³çšã1åãã㊠Eåããå³åŽãäœæ¥é åãšããŸãã
ãŸã㯠A2ïŒé ç®1ã®ãã«ããŠã³ïŒçšã® éžæè¢ã E2ããæšªã«åºåããããã§ããããã¹ã¿ ãšããããŒãã«åã䜿ã£ãŠã©ã®ãããªåŒãçµãã°ããã§ããããïŒ
ç°¡åã§ãããããããé¡ã§ãã£ãŠã¿ãŸãããã
Q1. ãã¹ã¿ã®é ç®1ïŒ1åç®ïŒã®ããŒã¿ã ãã«ããŠã³çšã®éžæè¢ãšããŠæšªæ¹åã«äžŠã¹ãŠåºåããã

ããŒãã«ã¯æ§é ååç §ãåºæ¥ãã®ãå©ç¹ã§ãé ç®1ïŒ1åç®ïŒã®ããŒã¿ã¯
=ARRAYFORMULA(ãã¹ã¿[é ç®1])
ããããèšè¿°ã§ååŸãã§ãããã§ãããããã ãšèŠåºããéãã±ãŒã¹ã§é¢åãªãã§ãã®ãèŠåºãåããäœ¿ãæžãæ¹ã¯äœ¿ããªããã®ãšããŸãã
E2ã«ã¯ã©ããªåŒããããã°è¯ãã§ããããïŒèããŠã¿ãŸãããïŒ
âââ
åçã¯ä»¥äž
âââ
A1. ãã¹ã¿ã®é ç®1ïŒ1åç®ïŒã®ããŒã¿ã ãã«ããŠã³çšã®éžæè¢ãšããŠæšªæ¹åã«äžŠã¹ãŠåºåããåŒ
åçã§ãã

=TOROW(UNIQUE(INDEX(ãã¹ã¿,,1)))
ãã¹ã¿ãã 1åç®ã INDEX颿°ã§ååŸããŠããããUNIQUE颿°ã§éè€æé€ãæåŸã«TOROW颿°ã§æšª1è¡ããŒã¿ã«å€æããã
ããã¯ç°¡åã§ããã

Googleã¹ãã¬ããã·ãŒãã®å Žåã¯ããã¹ã¿å šäœãåºåããå Žå㯠ARRAYFORMULAãä»ããå¿ èŠããããã§ãããINDEX颿°ã«ã¯ARRAYFORMULAãšåã广ãããã®ã§çç¥ããããšãã§ããŸãã
ãŸããGoogleã¹ãã¬ããã·ãŒãã®ãã«ããŠã³ã¯ èªåã§éè€æé€ããŠããããã§ãããç¡é§ã«æšªã«é·ãäœæ¥é åã«ãªã£ãŠããŸãããšãé¿ããçºã«ãUNIQUE颿°ã§éè€ãæé€ããŠåºåããŠããŸãã
ãããŸã§ã¯ 瞊1åããŒã¿ãªãã§ãæåŸã«æšªã«ããçºã« TOROWã䜿ããŸãã

ã±ãŒã¹ã«ãã£ãŠ åºåç¯å²ãå€ãã£ãŠããã®ã§ããã«ããŠã³ã®ç¯å²ã¯æšªã®ãå°»ãæ±ºããªãæžãæ¹
E2:2
ãšããŠãããŸãããã

1段ç®ã®ãã«ããŠã³ãåºæ¥ãŸããã
Step3. é ç®1ã§éžæããå€ã§é ç®2ã®ãã«ããŠã³ãçæãã

ç¶ã㊠1段ç®ã®éžæã§çµã蟌ãã é ç®2ã®éžæè¢ãäœæ¥é åã«è¡šç€ºãããŸãã
ãã ãããã§æ³šæãã¹ã㯠ãã®ãŸãŸäžã®ç»åã®ããã« é ç®2ã®éžæè¢ãåºåããŠããŸããšãé ç®1ã§éžæãããéèãããã«ããŠã³çšäœæ¥é åã«ãªãçºã

ãã®ããã«ãšã©ãŒè¡šç€ºãåºãŠããŸããŸãã
ç¡èŠããŠããããã§ããããã£ããä»åã¯æ¹è¯çãªãã§ ãšã©ãŒãåé¿ããçºã«

ãã®ããã« é ç®1ã空æ¬ã§ã¯ãªãïŒéžæãããïŒå Žåã¯ãé ç®2ã®åïŒå·ŠïŒã«é ç®1ã§éžæããå€ãä»ããŠåºåããããã«ããŸãã
ãPoint1ããã«ããŠã³ç¯å²å 㯠ç¯å²ã®é ã«ã€ã³ãŒã«ãä»ããŠèšå®ãããšçžå¯Ÿåç §ã«ãªã
ããã§é ç®1ãé ç®2ã®ãã«ããŠã³ãäžæ°ã«èšå®ããé

ç¯å²ã =E2:2ããšé ã« = ãä»ããã®ããã€ã³ãã§ãã
åçŽã«ã€ã³ãŒã«ãä»ããã« E2:2ãšèšå®ããŠããŸããš

ãå®äºãã§ä¿åãããš èªåã§ E2:2 ã§èšå®ããç¯å²ã¯ 絶察åç §ã®Â
=$E$2:$2
ãšçœ®ãæããããŠããŸããŸãã
ããã§ã¯è¡ãåãåºå®ãããŠããŸã£ãŠããã®ã§ã

ãã®ããã«ã©ã®ãã«ããŠã³ã åã E2:2ã®éžæè¢ã衚瀺ãããŠããŸããŸãã
ããã =E2:2ãš ãã«ããŠã³ç¯å²ã çžå¯Ÿåç §ã«ããããšã§

äœæ¥é åãè¡ããšã«éžæè¢ç¯å²ãšããŠèªã¿èŸŒã¿ãã〠é ç®1ã®æã¯ Eåãããé ç®2ã®æã¯äžã€ãºã¬ãŠFåãã ãšç¯å²ãçžå¯Ÿçã«ãºã©ã㊠èŠãŠãããŸãã
ããã«ãã£ãŠãé ç®1ã§éžæãããéèããšããéžæè¢ã¯ é ç®2ã®ãã«ããŠã³ã«ã¯è¡šç€ºãããªã仿§ãšãªããŸãã
ããã§ç¯å²æå®ã¯äžçºã§ãããŸããã
Q2. é ç®1ã§éžæããå€ãå·Šã«é£çµããŠãã¹ã¿ãçµã蟌ãã é ç®2ãæšªã«äžŠã¹ãã
ããã§ã¯èå¿ã® E2 ã«å ¥ããåŒãèããŸãããã
A2ã空æ¬ã ã£ãæã¯ãã=TOROW(UNIQUE(INDEX(ãã¹ã¿,,1)))
ã§ãã¹ã¿ã®é ç®1ãéžæè¢ãšããŠè¡šç€ºãã

A2ã空çœã§ã¯ãªãå Žåã¯ãA2ã®å€ãš ãã¹ã¿ã®é ç®1ãA2ã®å€ã§çµã蟌ãã é ç®2ããŠããŒã¯ã«ããå€ã暪ã«äžŠã¹ããã
ã©ããªåŒãäœãã°ããã§ããããïŒç¯å²ãšããŠåŒå ã§äœ¿ããã®ã¯ ããŒãã«åã®ãã¹ã¿ããã㊠A2 ã®ã¿ãšããŸãã
èããŠã¿ãŸãããïŒ
âââ
åçã¯ä»¥äž
âââ
A2. é ç®1ã§éžæããå€ãå·Šã«é£çµããŠãã¹ã¿ãçµã蟌ãã é ç®2ãæšªã«äžŠã¹ãã
åçã§ãã

=TOROW({A2,TOROW(UNIQUE(IF(A2="",INDEX(ãã¹ã¿,,1),
FILTER(INDEX(ãã¹ã¿,,2),INDEX(ãã¹ã¿,,1)=A2))))},1)
ããã¯å¹Ÿã€ãåŒã®æžãæ¹ãããã®ã§åçã®äžäŸãšæã£ãŠãã ããã
ãŸããA2ã»ã«ã®é£çµã¯çœ®ããšããŠãå ã«IFã®åå²åŠçãèããŸãããã
A2ã»ã«ã空çœãªã é ç®1ãA2ã»ã«ã 空çœã§ã¯ãªãïŒãã«ããŠã³ãéžæãããŠããïŒãªãããã¹ã¿ã®é ç®1ãA2ã®å€ã§çµã蟌ãã é ç®2ã ãŠããŒã¯ã«ããŠæšªäžŠã³ã«è¡šç€ºãããã
ãšããåå²ãªã®ã§ãããŠããŒã¯ã«ããŠæšªäžŠã³ã«è¡šç€ºãã¯ã©ã¡ãã®ãã¿ãŒã³ã§ãå¿ èŠãªå ±éã®åŠçã§ãããšããããã§ IFã¯
TOROW(UNIQUE(IF(A2="",
ãã®ããã« TOROWãšUNIQUEã®äžã«æžãã¡ãããŸãããã
空çœã ã£ãæã¯ãã¹ã¿ã®1åç®ãè¿ãã°ããã®ã§ãINDEX(ãã¹ã¿,,1)ãã§ããã
空çœã§ã¯ãªãæïŒç»åã®å Žåã ãšãéèããéžæããæïŒã¯ããã¹ã¿ã®é ç®1ãA2ãšäžèŽãããšããæ¡ä»¶ã§çµã蟌ãã æã® é ç®2ãåºåãããã®ã§ãFILTER颿°ã®åºçªã§ãã
FILTER(INDEX(ãã¹ã¿,,2),INDEX(ãã¹ã¿,,1)=A2)
ãã® FILTER颿°ã®äžã«IFãå ¥ãã¡ããæžãæ¹ããããŸãããåŠçãéããªãã®ã§ããã§ã¯é¿ããŠããŸãã

ãŸãã¯ãããŸã§åºæ¥ãŸããã
æåŸã« A2ã®é£çµã§ãããããã¯IFå ã§ã®é£çµãTOROWã§ç©ºçœé€å»ããŠããé£çµãäžã«ãã³ã§ã¯ãªã HSTACKãäœ¿ãæ¹æ³ãªã©è²ã æ¹æ³ã¯ãããŸãã
åçã ãš {ã,ã}ãã§æšªã«é£çµããŠãã æåŸã« TOROWãšããŠããŸãã
=TOROW({A2,TOROW(UNIQUE(IF(⊠)},1)
ãã®äžçªå€åŽã® TOROWã¯ç¬¬2åŒæ°ã1ã«æå®ãã 空çœãé€å»ããŠå·Šã«è©°ããçºã® TOROWã ãšèããŠãã ããã
ãšããããã¯2段éãã«ããŠã³ãã§ããŸããã
Step4. 3段éãã«ããŠã³ãèãã
ããããäžæ°ã«é£ãããªããŸãã

3段éç®ã®ãã«ããŠã³ãé£åãããã«ãããã以äžãèããå¿ èŠããããŸãã
ã»ä»ãäœæ®µéç®ãŸã§ãã«ããŠã³ãéžæãããŠããã®ãïŒ
ããããªãã¹ãã·ã³ãã«ã«èããçºã« ãã«ããŠã³ç¯å²ã®è¡ A2:C2ãèŠãŠãå€ã®å ¥ã£ãã»ã«ã幟ã€ãããïŒ ã§å€å®ããããšã«ããŸãããã
â»ãå¿ ããã«ããŠã³ã¯ å·Šããé ã«éžæããããšããéçšäžã®æ¡ä»¶ãå¿ èŠãšããŸãã
ããã ã£ãã COUNTA颿°ã§ç°¡åã«ååŸã§ããŸããã

ãããã æ±çšæ§ãé«ããããã«LET颿°ã䜿ã£ãŠãããŸãããã
LETã䜿ã£ãŠ

=LET(
 pr,A2:C2,
 m,ãã¹ã¿,
 c,COUNTA(pr),
ãã®ããã«çœ®ããŸãã
ãããã㚠仮㫠é ç®2ãŸã§éžæãããç¶æ ã®æ c 㯠2 ãšãªããæ¬¡ã¯é ç®3ã®ãã«ããŠã³ãéžã¶ããšã«ãªãã®ã§ãFILTERã§çµã蟌ãã§çµæãšããŠåºåããå㯠ãã¹ã¿ã®é ç®3ã®åãã€ãŸã

INDEX(m,,c+1) ãšãªããŸããããã col ãšãããŸãã
=LET(
 pr,A2:C2,
 m,ãã¹ã¿,
 c,COUNTA(pr),
 col,INDEX(m,,c+1),
çŽ æã®äžæºåãåºæ¥ãŠããŸãããïŒæçã«äŒŒãŠããŸãïŒ
次ã«IF颿°ã®åå²ãèããŸãã2段éã®æã¯ IF(A2="", ãšããŸãããã倿®µéã§èããå Žåã¯
ãŸã äœãéžæãããŠããªãç¶æ ã®æã¯ c ã€ãŸã COUNTA(pr) 㯠0ãšãªãã®ã§ããã㯠çåœå€ã ãš FALSEã«ãªããŸãã
ãã®æããã«ããŠã³ã®éžæè¢ãšãªãã®ã¯ ãã¹ã¿ã®é ç®1ã®åã§ãã

ã€ãŸããQ2ã®åçã®åŒã«ããŠã¯ãããš cã1以äžã®æïŒTRUEïŒã¯ FILTERã®åŒã§çµã蟌ãã col ã è¿ããcã 0ã®æïŒFALSEïŒã¯ããã®ãŸãŸ colãè¿ã
TOROW(UNIQUE(
 IF(c,Â
 FILTER(col,ãªããã®æ¡ä»¶åŒ),
 col )
))
ãã®çµæãUNIQUEããŠTOROWãæåŸã« pr ãšæšªé£çµããŠç©ºçœé€å»ã
ãããªåŒãäœãã°è¯ãããã§ããïŒ
ã§ã¯ããã®ããªããã®æ¡ä»¶åŒãéšåããããããŠã¿ãŸãããã
Q3. é ç®1ãé ç®2ã§çµã蟌ãã ãã¹ã¿ã®é ç®3ãéè€æé€ããŠæšªæ¹åã«äžŠã¹ãã

=LET(
pr,A2:C2,m,ãã¹ã¿,c,COUNTA(pr),col,INDEX(m,,c+1),
TOROW(UNIQUE(
IF(c,
FILTER(
col,
ãæ¡ä»¶åŒã
),
col
)
))
)é£åãã«ããŠã³ãå®çŸããåŒã®ãæ¡ä»¶åŒãã®éšåãèããŠã¿ãŸãããïŒãšãããé¡ã§ãã
ææŠããŠã¿ãŸãããïŒ
âââ
åçã¯ä»¥äž
âââ
A3. é ç®1ãé ç®2ã§çµã蟌ãã ãã¹ã¿ã®é ç®3ãéè€æé€ããŠæšªæ¹åã«äžŠã¹ãåŒ
åçã§ãã

=LET(
pr,A2:C2,m,ãã¹ã¿,c,COUNTA(pr),col,INDEX(m,,c+1),
TOROW(UNIQUE(
IF(c,
FILTER(
col,
BYROW(CHOOSECOLS(pr=m,SEQUENCE(c)),LAMBDA(r,AND(r)))
),
col
)
))
)æ¡ä»¶åŒéšåã¯
BYROW(CHOOSECOLS(pr=m,SEQUENCE(c)),LAMBDA(r,AND(r)))
ãããªåŒã§å®çŸã§ããŸããïŒä»ã®æžãæ¹ããããŸãïŒ
ãŸãFILTERå ã®åŒãªã®ã§ARRYAFORMLAãšåã广ããããé ååŠçãå¯èœã§ãã
pr=mãã®éšåã¯

ãã®ããã«ååäœã§ prãšmãäžèŽãããTRUEãè¿ãåŒãšãªã£ãŠããŸãã
ããã§å1ãå2ã®äž¡æ¹ãTRUEãšãªã£ãŠãã éšåãã€ãŸã éè ïŒ ãã³ãžã³ ãšãªã£ãŠããéšåã® å3ãåãåºãã°ããããã§ãã
ãããããš å¿ èŠãªã®ã¯å1ãå2ã ãã§ å3ã¯äžèŠã§ããã
ãªã®ã§ã
CHOOSECOLS(pr=m,SEQUENCE(c))
CHOOSECOLS颿°ãšSEQUENCE颿°ã§ 1åç®ã2åç®ã ããåãåºããŸãã
ã»ããšã¯Excelã¿ããã«TAKE颿°ããããšãããã§ãããã

c㯠prã®ç©ºçœã§ãªãã»ã«ã®æ°ãªã®ã§ãäžã®å Žåã¯2ãšãªããŸãã
SEQUENCE(2) 㯠{1:2} ãšãªãã®ã§ãããã CHOOSECOLSã®ç¬¬2åŒæ°ãšããããšã§ãpr=m ã®çµæé åã®1åç®ãš2åç®ã ããæœåºã§ããŸãã
ããšã¯ããã è¡æ¯ã«èŠãŠ å šãŠTRUEã®è¡ã ãTRUEãšãªã1åã®é åãäœãã°ããã®ã§ãBYROWã§è¡æ¯ã« AND颿°ãšããã°ããã§ãã

BYROW(CHOOSECOLS(pr=m,SEQUENCE(c)),LAMBDA(r,AND(r)))
ãã®åŒ ãåçã® ãæ¡ä»¶åŒãã§ããããšã¯FILTER颿°ãšçµã¿åãããŠ

FILTER( col, BYROW(CHOOSECOLS(pr=m,SEQUENCE(c)),LAMBDA(r,AND(r))) )
ãšããããšã§ãé ç®1ã éèãé ç®2ã ãã³ãžã³ ãšäžèŽããé ç®3ã ããååŸã§ããŸããã
FILTERã®çµæãš pr ãæšªã«çµåãã
ãã®FILTERã®çµæãUNIQUEããŠTOROWããŠããããã«ããŠã³ã§éžæããå€ãšæšªã«é£çµããŸãã
å ã»ã©ã®FILTERã®çµæã xãšçœ®ã㊠æåŸã«
TOROW({pr,x},1)
ãšããã°

ãã®ããã«æšªã«é£çµãã§ããŸããïœã
ãã ãããã ãš

ãã«ããŠã³ãæåŸã®3段ç®ãŸã§éžãã§ããŸããš ãšã©ãŒãåºãŠããŸããŸãã
ãã㯠ãã«ããŠã³ãå šãŠéžæãããš c ã€ãŸã COUNTA(pr) ã 3ãšãªããcol,INDEX(m,,c+1), ã¯INDEX(m,,4)ãšãªã£ãŠããŸãã3åã®ããŒã¿ã§ããm ã«ååšããªã 4åç®ãåãåºãããšããŠããçºã§ãã
æ°ã«ããªããŠããããã§ããããã㯠æåŸã®Â TOROW({pr,x},1) ã TOROW({pr,x},3) ãšããŠãTOROW㧠空çœãšãšã©ãŒã®äž¡æ¹ãç¡èŠããã° è§£æ¶ã§ããŸãã
ãšããããã§ äœæ¥ã¹ããŒã¹ã«åºåãã 3段éãã«ããŠã³åŒïŒå€æ®µéãã«ããŠã³åŒïŒã¯
=LET(
pr,A2:C2,m,ãã¹ã¿,c,COUNTA(pr),col,INDEX(m,,c+1),
x,TOROW(UNIQUE(
IF(c,
FILTER(
col,
BYROW(CHOOSECOLS(pr=m,SEQUENCE(c)),LAMBDA(r,AND(r)))
),
col
)
)),
TOROW({pr,x},3)
)ãã®ããã«ãªããŸãã

ãµã³ãã«ã¯3段éã§ããããã㯠4段éã5段éã®å€æ®µéãã«ããŠã³ã«ãæ¡åŒµå¯Ÿå¿ã§ããåŒã«ãªã£ãŠããŸãïŒ
Step5. è€æ°è¡ã®ãã«ããŠã³ã«ãäžã€ã®åŒã§å¯Ÿå¿ãã

è¡åäœã®ãã«ããŠã³åŒã¯å®æããŸããããä»åç®æããŠããã®ã¯äžã€ã®åŒã§å®çµãããããšã§ãã
ã€ãŸãäžã®ç»åã®ããã« 3段éã®é£åãã«ããŠã³ãã2è¡ç®ãã8è¡ç®ãŸã§ã® 7ã»ãããã£ãå ŽåããããE2 ã«äžã€åŒãå ¥ããã ãã§å®çŸãããã£ãŠããšã§ãã
ãããå®çŸã§ããã°å®æã§ããæåŸã®ãé¡ãã£ãŠã¿ãŸãããïŒ
Q4 . 1è¡å¯Ÿå¿ã®é£åãã«ããŠã³åŒã è€æ°è¡ã«å¯Ÿå¿ããåŒã«å€æŽããã
=LET(
pr,A2:C2,m,ãã¹ã¿,c,COUNTA(pr),col,INDEX(m,,c+1),
x,TOROW(UNIQUE(
IF(c,
FILTER(
col,
BYROW(CHOOSECOLS(pr=m,SEQUENCE(c)),LAMBDA(r,AND(r)))
),
col
)
)),
TOROW({pr,x},3)
)ãã®åŒãè€æ°è¡ ïŒç¯å² A2:C8ïŒã«å¯Ÿå¿ããããïŒãšãããé¡ã§ãã
ãšããããã A2:C8 ã®ã»ã«ç¯å²ã p ãšçœ®ããŠåŠçãããŸãããã
åºã ãéšåã¯
=LET(
p,A2:C8,m,ãã¹ã¿,ãããªããŸãã
èããŠã¿ãŸãããïŒ
âââ
åçã¯ä»¥äž
âââ
A4. 1è¡å¯Ÿå¿ã®é£åãã«ããŠã³åŒã è€æ°è¡ã«å¯Ÿå¿ããåŒã«å€æŽãã
åçã§ãã

=LET(
p,A2:C8,m,ãã¹ã¿,
BYROW(p,
LAMBDA(pr,
LET(c,COUNTA(pr),col,INDEX(m,,c+1),
x,TOROW(UNIQUE(
IF(c,
FILTER(
col,
BYROW(CHOOSECOLS(pr=m,SEQUENCE(c)),LAMBDA(r,AND(r)))
),
col
)
)),
TOROW({pr,x},3)
)
)
)
)LETããŠBYROWããŠããŸãLETããŠBYROWããã»ã»ã»ããªããªããããŒãªåŒã§ããã
ãã«ããŠã³é å A2:C8ïŒïœïŒãã 1è¡ãã€åãåºããŠåŠçããããã®ã§ BYROW颿°ã䜿ããŸãã
=LET(
p,A2:C8,m,ãã¹ã¿,
BYROW(p,
LAMBDA(pr,åãåºããè¡ã¯ pr ãšããŸããïŒãã£ãA2:C2 ãprãšããã®ã§ããã®åŸã®åŒã§ãã®ãŸãŸäœ¿ãããçºïŒ
ããã«è¡åäœã§ COUNTA(pr) ãæ¬¡ã®ãã«ããŠã³éžæè¢ãšãªã å INDEX(m,,c+1) ã倿°åããŠåŠçãããã®ã§ãBYROWã®äžã§ãã1å LET颿°ã䜿ããŸãã
LAMBDAãã«ããŒé¢æ°ã䜿ã£ãè€éãªåŒã§ã¯ãLETãå ¥ãåã§äœ¿ãã±ãŒã¹ãçµæ§ãããŸãã
=LET(
p,A2:C8,m,ãã¹ã¿,
BYROW(p,
LAMBDA(pr,
LET(c,COUNTA(pr),col,INDEX(m,,c+1),ããšã¯ BYROWå ã®LETåŒå ã§ å ã»ã©ã®åŒãåçŸããã°è¯ãã§ããã
=LET(
p,A2:C8,m,ãã¹ã¿,
BYROW(p,
LAMBDA(pr,
LET(c,COUNTA(pr),col,INDEX(m,,c+1),
x,TOROW(UNIQUE(
IF(c,
FILTER(
col,
BYROW(CHOOSECOLS(pr=m,SEQUENCE(c)),LAMBDA(r,AND(r)))
),
col
)
)),
TOROW({pr,x},3)
)
)
)
)
E2ã®äžã€ã®åŒã§è€æ°è¡ã®é£åãã«ããŠã³ãåŠçãããŠãŸããã
è€æ°è¡å¯Ÿå¿ã®é£åãã«ããŠã³åŒã®å®æã§ãïŒ
Step6. 4é£ãã«ããŠã³ãžæ¡åŒµå¯èœãïŒã確èªãã
ä»åäœæããé£åãã«ããŠã³åŒã®æ¡åŒµæ§ã確èªããŸãããã
3段éâ4段éãžã®æ¡åŒµã®æé ãèŠãŠãããŸãã
ãŸããã¹ã¿ã®æ¹ã4åæ§æã«å€æŽããŸãã
ããŒãã«ãªã®ã§ãèŠåºãè¡ã®äžçªå³ã®é ç®3ã®ç«¯ã«ã«ãŒãœã«ãåœãŠããš ïŒãã¿ã³ã§åãå³ã«æ¿å ¥ã§ããŸãã

èŠåºããé ç®4ãšããŠããšãããããµã³ãã«ãªãã§ ããŒã¯ãœããŒã ã 2è¡ã«ããŠ4段ç®ã®éžæè¢ãå ¥ããŠãããŸãããã

ã€ãã«ãã«ããŠã³ãããã·ãŒãã®æ¹ãæ¡åŒµããŸããä»åã¯äœæ¥ç¯å²ãšãã«ããŠã³ç¯å²ã1åãã空ããŠããªãã®ã§ãå ã«ç©ºçœåã1å远å ããŸãããã
ãã¡ãã¯ããŒãã«ã«ããŠããªãã®ã§ãDåãéžæããŠå³ã¯ãªãã¯ããå·Šã«1å远å ãšããŸãã

é ç®3ã® CåãDåã«ã³ããã㊠Dåã4段ç®ã®ãã«ããŠã³ã«ããŸãã

ãã«ããŠã³ç¯å²ã®éžææžã¿ã®ç®æãDeleteã§æ¶ããŠèŠåºããé ç®4ãšããã°OKã
ãã®æé ã ãšèªåã§ ãã«ããŠã³ç¯å²ãDåãŸã§çžå¯Ÿåç §ã§æ¡åŒµãããããšã«ãªããŸãã

æåŸã«åŒã®åé éšå =LET( p,A2:C8 ã® ãã«ããŠã³ã»ã«ç¯å²ã
=LET( p,A2:D8 ãšDåãŸã«ä¿®æ£ããã°å®æã§ãã

LET颿°ã䜿ããšæ°åŒã®ä¿®æ£ïŒã¡ã³ããã³ã¹ïŒãç°¡åã«ãªããŸããã
3段ç®ã§ããŒã¯ãœããŒãéžæããŠç¢ºèªãããš

4段éã®é£åãã«ããŠã³ã«ãªã£ãŠãŸããïŒ
倿®µéãã«ããŠã³ã®æ¡åŒµã確èªã§ããŸããã
Step7. ãã«ããŠã³ç¯å²ãããŒãã«åãã
ä»åäœæãã倿®µé£åãã«ããŠã³åŒã§ããããã«ããŠã³ç¯å²ã®æå®ã
=LET( p,A2:C8
ãš8è¡ç®ãŸã§ãšããŠããŸãã
ãã㊠=LET( p,A2:Cããšãå°»ïŒæçµè¡ïŒãæå®ããªãæžãæ¹ã«ããŠããªãã®ã¯çç±ããã£ãŠãæ°åŒã§ã¯åç §ããŠããã»ã«ããã«ããŠã³ãã©ããïŒãå€å¥ã§ããªãçºã§ãã

ããç¯å²ã A2:CãšããŠããŸããšããã«ããŠã³èšå®ãããŠããªãè¡ãå«ãã·ãŒãã®æçµè¡ãŸã§ 1段ç®ã®ãã«ããŠã³éžæè¢ã衚瀺ããç¡é§ãªèšç®ãå€ãç¶æ ãšãªããŸãã
åŒå ã§æçµè¡ãåºå®ãã åç §ãããã«ããŠã³ç¯å²ãå€åã«ããæ¹æ³ãããã«ããŠã³ç¯å²ã®ããŒãã«åã§ãã

èŠåºãè¡ãå«ããã«ããŠã³ã®ã»ã«ç¯å²ãéžæããç¶æ ã§
å³ã¯ãã¯ ïŒ ããŒãã«ã«å€æ
ã§ããŒãã«åããŠé©åœãªããŒãã«åãã€ããŸãã

æ°åŒåŽã®åé
=LET( p,A2:C8ãã®ç®æãã=LET( p,ãã«ããŠã³ããŒãã«_1
ãšããŒãã«åæå®ã«ãããš

ãã®ããã«ããŒãã«ã®ç¯å²ïŒãã«ããŠã³ã®ç¯å²ïŒãŸã§æ°åŒã®çµæãå±éãããŸãã
ãã«ããŠã³ãæ¡åŒµãããå Žåã¯ãããŒãã«ãªã®ã§ æçµè¡ã«ãã€ã³ã¿ãããŠãæã«è¡šç€ºããã + ãã¿ã³ãã

ãã®ããã«è¡ã远å ãããš ããŒãã«ã®æ¡åŒµã«äŒŽãããã«ããŠã³ãæ¡åŒµãããåãããŠæ°åŒç¯å²ãæ¡åŒµãããŸãã䟿å©ïŒ
ãã ããã«ããŠã³ç¯å²ãããŒãã«åããŠããŸããšãå ã»ã© Step6ã§è§Šãã 3é£ â 4é£ ã®ãã㪠åã®æ¡åŒµã«ãããã«ããŠã³ã®çžå¯Ÿåç §ã«å¯Ÿå¿ãåºæ¥ãŸããã
ããŒãã«å ã®ãã«ããŠã³ã¯ãã»ã«ç¯å²ã«å¯ŸããŠã®ããŒã¿ã®å ¥åèŠå¶ã§ã¯ãªã


åã®åãšããæ±ãã«ãªã£ãŠããçºã§ãã
ããã ãšãã«ããŠã³ç¯å²ãåæ¹åïŒæšªæ¹åïŒã«çžå¯Ÿåç §ããŠãããªãçºããã«ããŠã³ã®é£åããæ®µæ°ãæ¡åŒµããæã«ã¡ãã£ãšé¢åã§ãã
ãŸãéçšäžã«ãã«ããŠã³ã®é£åæ°ãå€ããããšã¯ããŸãç¡ããã§ãå¯èœã§ããã°ãã«ããŠã³ç¯å²ã®ããŒãã«åã¯ãã¹ã¹ã¡ã§ãã
ä»åã®é£åãã«ããŠã³åŒã®ã¡ãªããã»ãã¡ãªãã
æåŸã«ãŸãšãã§ãã
ãã«ããŠã³ç¯å²ãããŒãã«åãã 倿®µéé£åãã«ããŠã³åŒ
=LET(
p,ãã«ããŠã³ããŒãã«_1_,m,ãã¹ã¿,
BYROW(p,
LAMBDA(pr,
LET(c,COUNTA(pr),col,INDEX(m,,c+1),
x,TOROW(UNIQUE(
IF(c,
FILTER(
col,
BYROW(CHOOSECOLS(pr=m,SEQUENCE(c)),LAMBDA(r,AND(r)))
),
col
)
)),
TOROW({pr,x},3)
)
)
)
)â»ãã«ããŠã³ããŒãã«_1 㯠ãã«ããŠã³ãèšå®ããããŒãã«
â»ãã¹ã¿ã¯ å¥ã·ãŒãã®éžæè¢ã®ãã¹ã¿ããŒã¿ã®ããŒãã«

ãã®ããã«ããŒãã«æ©èœãšã·ãŒã颿°ãé§äœ¿ããã¡ãã£ãšè€éãªåŒãçµã¿åãããããšã§ã倿®µéé£åãã«ããŠã³ãå®çŸã§ããŸãã
ãã®æ¹åŒã§é£åãã«ããŠã³ãå®è£ ããã¡ãªãã
â æ°åŒãå
¥ããç®æã1ã«æã ãã§ãã
ãåŒãå
¥ããã»ã«ã¯1ã€ã ããªãã§ãã³ããã§äœ¿ããŸãã
ããŸããLET颿°ã§æå®ããç¯å²ã¯2ãµæã ããªã®ãç°¡åã§ããã
â ãã«ããŠã³ã®èšå®ã1åã ãã§ãã
ããã«ããŠã³åããã»ã«ç¯å²ãæå®ããŠãããŒã¿ã®å
¥åèŠå¶ã§ ãã«ããŠã³ïŒç¯å²å
ïŒã§ã äœæ¥ç¯å²ã®è¡ã çžå¯Ÿåç
§æå® äŸïŒ =E2:2
â ã·ãŒããã³ããŒããŠãã®ãŸãŸäœ¿ãã
ãåããã¹ã¿ã䜿ã£ãé£åãã«ããŠã³ãä»ã®ã·ãŒãã§äœ¿ãããå Žåããã·ãŒããã³ããŒããã ãã§ç°¡åã«äœ¿ããŸãã
â åè¡ã®ãã«ããŠã³ãç¬ç«ããŠããã®ã§ãè€æ°äººã§åæã«äœ¿ãã

å ±æããŠåæã«äœæ¥ããŠããå Žåã§ããè€æ°äººãããããèªåã®è¡ã®ãã«ããŠã³ãæäœã§ããŸãããŸããéžæéäžã®è¡ããã£ãŠãä»ã®è¡ã«ã¯åœ±é¿ããããŸããã
ãšããããã§ã以åã®3段éé£åãã«ããŠã³åŒã«æ¯ã¹ããšç°¡åãã€å©äŸ¿æ§ãåäžããŠãããšèšããŸãã
ãã®æ¹åŒã§é£åãã«ããŠã³ãå®è£ ãããã¡ãªãã
äžæ¹ã§ãã¡ãªããããããŸãã
â ãã«ããŠã³ã® è¡åäœå äœæ¥ã»ã«ãå¿ èŠ

åé ã«ãæžããŸããããåè¡ã®ãã«ããŠã³ã«å¯ŸããŠäœæ¥é åãå¿ èŠã«ãªããŸããåã®é衚瀺ãªã©ã䜿ã£ãŠèŠããªãããã«ããŸãããã
â éžæããªãããäžäŸ¿

ããã®ããã«äžåºŠéžæãããã«ããŠã³ãåéžæããããšãããšãå³åŽã®éžæè¢çšã®äœæ¥é åã¯æ¬¡ã®ãã«ããŠã³çšã«ãªã£ãŠããŸã£ãŠããçºãä»ã®é ç®1ã®éžæè¢ãåºãŠããŸããã
äžåºŠéžæãããã«ããŠã³ã倿ŽããïŒéžæããªããïŒå Žåã¯ãäžæŠDeleteã§ãã«ããŠã³ã空ã«ããŠããåéžæããå¿ èŠãããã£ãŠããšã§ãã
ãããäžçªã®æ¬ ç¹ã§ããã
â ãã«ããŠã³ãèªç±ã«é 眮ã§ããªã

ãã®æ¹åŒã¯è¡æ¹åã¯ééããããŠãã«ããŠã³ãé 眮ããããšã¯å¯èœã§ããïŒãã ããã«ããŠã³ç¯å²ã«ããŒãã«ã¯äœ¿ããªãïŒ
ããããåæ¹åã¯åŒã«åœ±é¿ãããã®ã§ ééããããããšã¯ã§ããŸããã

åæ§æã¯ãã¹ã¿ãšåããšããå¿ èŠãããã£ãŠããšã§ãã
ãã¡ãªããã«å¹Ÿã€ãè§ŠããŸããããéã«èšãã°ãã®çšåºŠã®ãã¡ãªãããªãã§ãèæ ®ããŠå©çšããã°ååã«æŽ»çšã§ããæ¹åŒã ãšæããŸãã
ããã è€æ°éžæå¯èœãªé£åãã«ããŠã³ãå®çŸã§ããïŒïŒ
ä»åã¯é£åãã«ããŠã³åŒã®æ¹è¯çã玹ä»ããŸããã
ä»åã®ãã«ããŠã³ã¯ãããŸã§ã åäžéžæã®åŸæ¥ã®ãã«ããŠã³ã§å©çšããããšãæ³å®ããŠããŸããïŒããã衚瀺ãç¢å°è¡šç€ºãã¯èªç±ïŒ
2024幎8æã«Googleã¹ãã¬ããã·ãŒãã«å®è£ ããã è€æ°éžæå¯èœãªãã«ããŠã³ã§ã¯äœ¿ããŸããã
ã§ããæ°åŒã工倫ããã°ãé ç®1ã§ èé¡ãšéèãéžæããããé ç®2ã§ è±èãé¶èãçèããã³ãžã³ããã£ãããã¿ããã®ã»ã»ã» ãš éžæãã2ã€ã®é ç®ã§çµã蟌ãŸãããªã¹ãã衚瀺ããããã¿ãããªããšãåºæ¥ããããããŸããã
æ©äŒãããã° è€æ°éžæã§ã®é£åãã«ããŠã³ã«ãææŠããŠã¿ãããšæããŸãã
次åã®ãã¿ã¯ã»ã»ã»ãããã 匷åãªæé¢æ°ãåãäžãããããªãšãïŒãŸã æªå®ã§ãïŒ
- #Excel
- #ã¹ãã¬ããã·ãŒã
- #GoogleWorkspace
- #ããŒãã«
- #Googleã¹ãã¬ããã·ãŒã
- #and
- #lambda
- #index
- #Take
- #unique
- #Let
- #filter
- #ARRAYFORMULA
- #sequence
- #ãã«ããŠã³
- #絶察åç §
- #çžå¯Ÿåç §
- #ããããããŠã³
- #BYROW
- #ããŒãã«æ©èœ
- #æ§é ååç §
- #COUNTA
- #ãã«ããŠã³ãªã¹ã
- #ããããããŠã³ãªã¹ã
- #CHOOSECOLS
- #TOROW
- #é£åãã«ããŠã³