Last active
August 15, 2018 09:58
-
-
Save kyoh86/8a4c511189db4c4cfdfd4d194e962d86 to your computer and use it in GitHub Desktop.
Spreadshsetチートシート
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
結構arrayformula便利だけど忘れがちだからメモっていく |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 1行目だけタイトル表示で、以降arrayformulaの数式適用 | |
=arrayformula(if(row($A:$A)=1,"タイトル",$A:$A&" with arrayformula")) | |
// サンプル:https://docs.google.com/spreadsheets/d/1f7MvbvA92iCpBJ45acCAfejrhzjX5u3WvmLsbsAuFzk/edit#gid=0 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// A列に重複がないかチェックして、重複しているものにはエラーを表示する数式 | |
=arrayformula(if(match($A:$A,$A:$A,0)=row($A:$A),"","duplicate for "&match($A:$A,$A:$A,0))) | |
// サンプル:https://docs.google.com/spreadsheets/d/12QRQUxvd-ZYmjc8koGMH4KY8OBvsRK-sMk2qxZBVjX0/edit#gid=0 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// こんなシートがあったとき | |
好物表 | |
| A | B | | |
1| Bob | Apple | | |
2| Alice | Banana | | |
// 次のようなリストがあったとする | |
お土産フルーツリスト | |
| A | | |
1| Apple | | |
2| Banana | | |
3| Cherry | | |
// 「そうだ、みんなの好物の表があったから、好きな人にあげよう!」 | |
// と思って、vlookupしようとするけど、好物表のA列とB列が逆なので、フルーツ→好きな人 で紐付けられない | |
// そんな時に。 | |
=vlookup('お土産フルーツリスト'!$A1, {'好物表'!$B:$B,'好物表'!$A:$A}, 2, false) | |
// {'好物表'!$B:$B,'好物表'!$A:$A} がキモ。 | |
サンプル:https://docs.google.com/spreadsheets/d/14r_VIogKG7-p0XIar-tOOvwElh_n-4VJZc-9U7pCesY/edit#gid=979201703 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// isblankがTrueになるようなやつ | |
=iferror(0/0) | |
// 試しに下のようにして見ると効果が分かる | |
A1:="" B1:=isblank(A1) | |
A2:=iferror(0/0) B2:=isblank(A2) | |
// サンプル:https://docs.google.com/spreadsheets/d/1E7y2huxHsQ_DnDDn412Q-bwiiNdiJ9dAEfxtc0G4gZ4/edit#gid=0 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment