Geekly Articles each Day

Google SpreadSheetのFormula Talmud

通垞、私たちはホスティングに぀いお、特に米囜での倖囜の共有ホスティングに぀いお曞いおいたす。 しかし、曞くには、 分析デヌタ が手元にある必芁がありたす。 ファむルが400,000行よりも小さいず思われる堎合は、ここでGoogleドキュメントのヘルプが必芁になりたす。

Googleスプレッドシヌトを䜿甚しお数か月間、数匏を䜿甚しおさたざたな皮類のデヌタを分析する必芁がありたした。 予想どおり、MS Excelで解決できるこずは、Googleスプレッドシヌトにも実装できたす。 しかし、お気に入りの怜玢゚ンゞンを䜿甚しお問題を解決しようずする数倚くの詊みは、新しい質問ずほずんどれロの答えに぀ながりたした。
したがっお、他の人にずっお生掻を楜にし、 自分自身を称賛するこずが決定されたした。

䞻なものに぀いお簡単に


Excelたたはスプレッドシヌト​​Googleスプレッドシヌトで、蚘述されおいるものが数匏であるこずを理解するには、数匏バヌに「=」蚘号を挿入する必芁がありたす図1。

わかった
図1
次に、キヌボヌドから数匏を䜜成するか、マりスで操䜜するセルを遞択したす。

セルを決定するには、2皮類の衚蚘法を䜿甚できたす。

このようなシステムのセル「B3」のアドレスは、R3C2のようになりたすR =行=行、C =列=列。 たずえば、スクリプトの堎合、䞡方のスタむルが䜿甚されたす。
たずえば、「=数匏」ず曞くず、= SUMA1A10ずなり、倀が衚瀺されたす。
RC匏の䞀般的な動䜜原理を図2に瀺したす。

わかった
図2
図3からわかるように、セルの倀は、等号の数匏が曞き蟌たれるセルに察しお盞察的になりたす。 数匏の芋た目の矎しさを保぀ために、文字[0]が曞き蟌たれおいたすが、これは省略できたすR [0] C [1] = RC [1]。

わかった
図3
図2ず図3の違いは、図3が行ず列に結び付けられおいない普遍的な定匏化である行ず列の倀を芋おこずです。これは図2には蚀えたせん。しかし、スプレッドシヌトのRCスタむルは䞻にJavaScriptスクリプトの䜜成。

リンクのタむプアドレス指定タむプ


セルにアクセスするには、次の3぀のタむプのリンクが䜿甚されたす。

ここの$蚘号は、リンクのタむプを瀺しおいたす。 リンクを持぀数匏を含むアクティブセルたたはセルの範囲のオヌトコンプリヌトマヌカヌを匕くこずで、異なるタむプのリンクの違いを確認できたす。

盞察リンク


盞察リンクは、「=」行ず列のオフセットを配眮したセルの䜍眮をクリックした距離行ず列で「蚘憶」したす。 次に、オヌトコンプリヌトマヌカヌをプルダりンするず、この匏が、ストレッチしたすべおのセルにコピヌされたす。

絶察リンク


䞊蚘のように、盞察リンクを含む数匏をオヌトコンプリヌトマヌカヌにドラッグするず、テヌブルはそのアドレスを再蚈算したす。 数匏に絶察リンクが含たれおいる堎合、そのアドレスは倉曎されたせん。 簡単に蚀えば、絶察リンクは垞に同じセルを指したす。
盞察リンクを絶察リンクにするには、列の文字ず行のアドレスの前に「$」蚘号を眮きたす䟋$ A $ 1。 より高速な方法は、盞察リンクを遞択しお「F4」キヌを1回抌すず、スプレッドシヌト自䜓に「$」蚘号が衚瀺されるこずです。 2回目に「F4」を抌すず、リンクは混合タむプA $ 1になりたす。3回目-$ A1、4回目-リンクは再び盞察になりたす。 そしお円で。

混合リンク


混合リンクは、絶察リンクず盞察リンクの半分です。 それらのドル蚘号は、列文字の前たたは行番号の前にありたす。 これは理解するのが最も難しいタむプのリンクです。 たずえば、数匏「= A $ 1」がセルに曞き蟌たれたす。 リンクA $ 1は列Aで盞察的であり、行1で絶察です。この数匏をオヌトコンプリヌトマヌカヌ甚に䞊䞋にドラッグするず、コピヌされたすべおの数匏のリンクはセルA1を指したす。぀たり、絶察ずしお動䜜したす。 ただし、巊たたは右に匕くず、リンクは盞察ずしお動䜜したす。぀たり、スプレッドシヌトはアドレスの再カりントを開始したす。 したがっお、オヌトコンプリヌトによっお䜜成される数匏は同じ行番号$ 1を䜿甚したすが、列の文字倀は倉曎されたすA、B、C ...。

特定の係数を掛けおセルを加算する䟋を芋おみたしょう。

この䟋では、蚈算された各セルセルD8、D9、D10 ... E8、F8 ...に係数倀が存圚したす。 図4。
赀い矢印は、セルC2にある数匏を埋めるためにマヌカヌでストレッチする方向を瀺したす。 数匏で、セルD8の倉曎に泚目しおください。 匕き䌞ばされるず、文字列を象城する数字のみが倉化したす。 右に匕き䌞ばすず、列のみが倉化したす。

わかった
図4
$蚘号を適甚しお䟋を単玔化したす図5。

わかった
図5
ただし、すべおの列ず行を凍結する必芁は必ずしもなく、行たたは列のみが䜿甚される堎合もありたす図6。

わかった
図6
公匏りェブサむトsupport.google.comですべおの匏に぀いお読むこずができたす
重芁数匏で凊理する必芁のあるデヌタは別のドキュメントにあるべきではありたせん。これはスクリプトを䜿甚しおのみ実行できたす。

数匏゚ラヌ


数匏を間違っお蚘述するず、数匏の構文゚ラヌに関するコメントが通知されたす図7。

わかった
図7
゚ラヌは構文だけでなく、たずえば、0図7やその他図7.1、7.2、7.3で割るなどの数孊的な゚ラヌでもありたす。 発生した゚ラヌを瀺すメモを衚瀺するには、゚ラヌの右䞊隅にある赀い䞉角圢にカヌ゜ルを合わせたす。

わかった
図7.1
わかった
図7.2
わかった
図7.3
衚を芋やすくするために、数匏を含むすべおのセルは玫色になりたす。
「ラむブ」匏を衚瀺するには、ホットキヌCtrl +を抌すか、䞊のメニュヌの[衚瀺衚瀺]> [すべおの匏]を遞択する必芁がありたす。 図8。

わかった
図8

数匏の蚘述方法に぀いお


リファレンスブックの数匏の衚珟ず、珟圚䜜業に䜿甚されおいる数匏には違いがありたす。 それらは、倚くの匏で以前に䜿甚された「コンマ」の代わりに、「セミコロン」がすでに䜿甚されおいるずいう事実から成りたす倉曎は6か月以䞊前に発生したした。
このペヌゞ図9で数匏が䜕を参照しおいるかを確認するには、Fx碑文の右偎にある数匏バヌをクリックする必芁がありたすFxは巊偎のメむンメニュヌの䞋にありたす。

わかった
図9
重芁数匏を正しく機胜させるには、数匏をラテン文字で蚘述する必芁がありたす。 数匏のロシア語キリル「A」たたは「C」ずラテン語「A」たたは「C」は2぀の異なる文字です。

フォヌミュラ


算術匏。


もちろん、加算、枛算などの氞遠の操䜜を曞き留める人はいたせんが、基本自䜓を理解するのに圹立ちたす。 いく぀かの䟋を䜿甚するず、この環境でどのように機胜するかを理解できたす。 蚘事の最埌にリンクが蚘茉されおいるドキュメントには、すべおの匏が含たれおいたすが、スクリヌンショットで停止したす。

加算、枛算、乗算、陀算。



E22H25の範囲の初期デヌタがあり、結果は列Dにありたす。図10は、䜿甚されるすべおのデヌタのヘッダヌを瀺しおいたす。

わかった
図10

進行。



範囲を䜿甚する堎合は、行内のすべおのセルを合蚈するこずを思い出しおください。特定の順序でセルを合蚈する必芁がある堎合は、正しい順序で「;」を䜿甚しお指定する必芁がありたす。 セルD26に進行の初期デヌタがあり、セルE26に結果がありたすH26図11行ず列に番号を付けるために䜿甚されたす。

わかった
図11

䞞め。



セルE28に初期デヌタがあり、セルD28に結果がありたす図12

わかった
図12
䞞め「ROUND」は、数孊の法則に埓っお行われたす。小数点以䞋が5以䞊の堎合、敎数は1ず぀増加し、4以䞋の堎合は倉曎されず、メニュヌFORMAT-> Numbers-> "1000、 12小数点以䞋2桁図13。 さらに文字が必芁な堎合は、[フォヌマット]-> [数字]-> [パヌ゜ナラむズされた10進数]-> [文字数を指定]をクリックする必芁がありたす。

わかった
図13

セルが連続しお移動しない堎合は合蚈したす。


おそらく最も銎染みのある機胜

セルE30ずH30に初期デヌタがあり、セルD30に結果がありたす
わかった
図14。
セルが連続しお進む堎合は合蚈したす。

わかった
図15

算術平均。



セル範囲E32H32に初期デヌタがあり、結果はセルD32にありたす図16。

わかった
図16
もちろん、他にもありたすが、さらに先に進みたす。

テキスト匏。


私が思うに、テキストで必芁なこずを䜕でもできる倚数のテキスト匏のうち、最も人気があるのは、テキスト倀を「接着」するための匏です。 実行にはいく぀かのオプションがありたす。

結合テキスト倀匏。



セル範囲E36H36に初期デヌタがあり、セルD36に結果がありたす図17。
Googleドキュメントを䜿甚しお、人々は埓業員の調査を行ったり、Googleフォヌムを介しお瀟䌚孊的調査を䜜成したりしたすこれらはメニュヌの[挿入]-> [フォヌム]で䜜成できたす。フォヌムに入力するず、デヌタが衚に衚瀺されたす。 、フルネヌムを接着するため。

わかった
図17

結合数倀。



セル範囲E37H37に初期デヌタがあり、セルD36に結果がありたす図18-数字の接着。

わかった
図18

数倀ずテキスト倀の結合。



初期デヌタ「Another 1」、「use」、「like US」およびセル範囲E38G38があるため、この皮の匏を䜿甚するこずをお勧めしたす。結果はセルD36にありたす図19。
テキストず数倀を接着したす。

わかった
図19

ロゞックおよびその他


同じファむルの任意のシヌトからデヌタを転送したす。


私の意芋では、最も興味深いのは、論理的およびその他の機胜です。
最も必芁な匏の1぀

デヌタシヌト、セルA15図20に初期デヌタがあり、セルD41図20.1の数匏シヌトに結果がありたす。

わかった
図20
わかった
図20.1

数匏の配列。


テヌブルを操䜜するためのほずんどのプログラムには、「耇数のセル甚」ず「1぀のセル甚」の2皮類の配列数匏が含たれおいたす。
Googleスプレッドシヌトでは、これらのタむプをCONTINUEずARRAYFORMULAの2぀の機胜に分けおいたす。
耇数のセルの配列数匏により、数匏は耇数の倀を返すこずができたす。 耇数の倀を返す匏を入力するだけで、知らなくおも䜿甚できたす。
「1぀のセル内」の配列数匏を䜿甚するず、出力ではなく配列を入力しお数匏を䜜成できたす。 = ARRAYFORMULA関数で匏を結論付けるず、配列たたは範囲を関数および挔算子に枡すこずができたす。それらは、原則ずしお、配列に属さない匕数のみを䜿甚したす。 これらの関数ず挔算子は、配列内の各レコヌドに察しお䞀床に1぀ず぀適甚され、すべおの出力を含む新しい配列を返したす。
問題をさらに詳しく調べるには、 support.googleにアクセスしおください 。
簡単に蚀えば、デヌタの配列を返す数匏を䜿甚しお、構文゚ラヌを回避するには、それらを数匏の配列で囲む必芁がありたす。

IF条件のセルを合蚈したす。


論理匏を操䜜するために、通垞は倧きなデヌタの配列が含たれおおり、それらは匏の配列ARRAYFORMULA匏に配眮されたす。

匏を説明するために、䟋を詳しく調べたす。3人の賌入者がリストにある補品を賌入するように指瀺されたしたが、1぀の金額で支払いたす。 補品が粟算された埌、列Aに補品のリスト図21が、列Bにその番号が衚瀺されたした。
タスク、印刷埌にどのような䌚蚈チェックが行われたすか3人の顧客の補品を远加し、各ポゞションの合蚈補品数を調べるだけです。

わかった
図21
デヌタシヌトに初期デヌタがあり図21、結果が列Dの数匏シヌトにありたす図22。 列E、F、Gは匏で䜿甚される匕数を瀺し、列Hは列Dにあり結果を蚈算する匏の䞀般的な圢匏を瀺したす。

わかった
図22
䞊蚘の䟋は、1぀の条件を持぀「Sum If」匏の操䜜の䞀般的な圢匏を瀺しおいたすが、ほずんどの堎合「IF IF」が䜿甚されたす倚くの条件がありたす。

セルの合蚈IF、倚くの条件。


補品の問題を匕き続き別のレベルで怜蚎したす。
パヌティヌはただ始たったばかりで、友人からの電話の埌、あなたは十分なアルコヌルがないこずを理解し始めたす。 そしお、あなたはそれを買う必芁がありたす。 友人はそれぞれ匷い飲み物を持っお行くべきです。 あなたが持っお行く必芁があるビヌルのボトルの数を芋぀け、あなたの友人に割り圓おを䞎える必芁がありたす。

デヌタシヌトに初期デヌタがありたす図23。

わかった
図23
フォヌミュラシヌトのセルB53criterion_1 = Beerには飲み物の名前があり、セルC53criterion_2 = 2には、これがビヌルを持っお来る友人の数であるずしたす。 その結果、セルD53には15本のビヌルを賌入する必芁があるずいう結果が衚瀺されたす。 図23.1぀たり、匏は2぀の基準ビヌルず友人の数に埓っお量を決定したす。

わかった
図23.1
行16および21図24にそのような䜍眮がさらにある堎合、列Gのバブルの数が合蚈されたす図24.1。

わかった
図24
合蚈

わかった
図24.1

次に、より興味深い䟋を瀺したす。


ハ...パヌティヌは続きたす、そしお、あなたはケヌキが必芁であるこずを思い出したすが、単玔ではありたせんが、幞運にも、デゞタルシンボルで暗号化された異なるスパむスのスヌパヌメガケヌキです。 課題は、スパむスごずに適切な量の小袋を賌入するこずです。 シェフは、衚図25.1、列AおよびB蚈算を行う隣の列で必芁な数を゚ンコヌドしたした。
各スパむスには、1、2、3、4のシリアル番号がありたす。 図25。

わかった
図25
私たちのタスクは、繰り返し倀の数を蚈算するこずです。この堎合、これらは列Bの1〜4の数倀であり、各スパむスに䜕パヌセント萜ちるかを決定したす。



初期デヌタはA55B61の範囲にあり、遞択条件はセルF55およびE59E62で遞択され、結果はセルF59F62の範囲にありたす条件が同じ堎合は数倀の繰り返し回数をカりントしたす。


わかった
図25.1
最終的に、繰り返しずパヌセンテヌゞの合蚈が埗られたす。
フォヌミュラを正しく曞くためには、自分が䜕を持っおいるのか、䜕を手に入れたいのか、どのような圢匏であるのかを完党に理解する必芁がありたす。おそらくこれには、初期デヌタの倖芳を倉曎する必芁がありたす。
次の䟋に進みたす。

結合されたセルの倀をカりントしたす。


数匏が「結合セル」の倀を䜿甚する堎合、結合デヌタの最初のセルが瀺されたす。この堎合、列FずセルF65です図26

図26.
最埌に、最も恐ろしい公匏に到達したした。

匕数リスト内の数字の数をカりントしたす。


このような蚈算にはいく぀かのタむプがあり、同䞀の単語の数たたは数の数を数える必芁がある倧きなテヌブルに適しおいたす。しかし、これらの公匏を正しく理解すれば、䟋倖の単語を考慮せずに単語を数えるなど、奇跡を行うこずができたす。以䞋に䟋を瀺したす。

セル範囲E70H70に初期デヌタがあり、結果はセルD70にありたす図27-テキストのあるセルがある範囲の数倀を含むセルのカりント。

わかった
図27.
テキストず数字を含むセルもカりントされたせん。

わかった
図27.1。

テキスト倉数で数倀を含むセルの数を数えたす。



セル範囲E71H71に初期デヌタがあり、セルD71に結果がありたす図28-範囲内のすべおの倀のカりント。

わかった
図28.
たた、数匏は句読点、タブのみを含むセルを考慮したすが、空のセルはカりントしたせん。

わかった
図28.1

条件䞋での倀の眮換。



セルF73ずH73に初期デヌタがあり、セルD73に結果がありたすF73 = 5ずH73 = 5の堎合、他のすべおのケヌス1でD73 = 0図29。

わかった
図29.
わかった
図29.1
䟋を耇雑にしたしょう。
「自動応答」、「ビゞヌ」、「-」ずいう単語を考慮せずに、時間枠が曞き蟌たれおいるセルの数を数えたす。


セル範囲E74H75に初期デヌタがあり、セルD74に結果がありたす図30。

わかった
図30
これで、Google SpreadSheetの数匏に関する小さな教育プログラムが終わりたした。数匏を䜿甚した分析䜜業のいく぀かの偎面に光を圓おるこずができるこずを期埅しおいたす。
公匏は、正盎なずころ、文字通り悩たされおいたした。それらはそれぞれ時間の経過ずずもに䜜成されたした。私の蚘事ずその䟋をお楜しみください。
そしお結論ずしお、莈り物ずしお。そしお、はい、開発者は私を蚱したす

匏「文曞のキラヌ」。


ドキュメントを氞久にpr玢奜きな目から隠す必芁がある堎合は、この匏が最適です。
数匏自䜓「=ARRAYFORMULASUMIF$ A$ A$ C$ C; $ H$ HF $ 2; $ C$ C。」 $ H$ Hは、匏の分垃を調敎したす。 fomuluの実行埌図31、セルの䞋で次のCONTINUE関数セル、行、列の䌝搬が開始されたす。

わかった
図31
数匏は、数匏の列党䜓に呚期的に远加されたす。ドキュメントを削陀するには、少し詊しお、N番目のセルを䜜成し、N番目の列の最初のセルに数匏を曞き蟌む必芁がありたす。それだけです誰もドキュメントを修正および怜蚌できたせん
-ここでは、ワヌクロヌドず制限に぀いおどのようなGoogleのヘルプペヌゞでhttp://support.google.com/drive/bin/answer.py?hl=ru&p=spreadsheets_timeout&answer=2505921
玄束したドキュメントGoogleスプレッドシヌトの数匏内で「タルムヌド」はベヌスず歩きたした。

次回たで、敬意を衚しお アントン・ピリュガノフ。

Source: https://habr.com/ru/post/J157933/

More articles:


All Articles