一般的に使用される Excel 関数の式は何ですか? 職場では、Excel はデータの統計分析に最もよく使用されます。ある程度のスキルを習得していないと、オフィスでの作業中に間違いなく他の人に助けを求めることになります。 、エディターは、職場で助けを求めるのをやめるのに役立つ 43 個の Excel 関数の式を提供します。
これは、Excel 内で事前定義され、特定の順序と構造で計算、分析、その他のデータ処理タスクを実行する機能モジュールです。したがって、関数は「特殊数式」と呼ばれます。数式と同様に、Excel 関数の最終的な戻り結果は値です。関数には、大文字と小文字が区別されない一意の名前があり、その名前によってその機能と目的が決まります。
簡単な例を挙げると、テーブルを処理するときに、名前の最初の文字をすべて大文字に変更するにはどうすればよいでしょうか。
機能が分からない場合は、手作業で一つ一つ修正するのでしょうか？適切な機能を知っていれば、いちいち変更する必要はありません。関数式を入力して3秒で完了！
数式はユーザーが設計した計算であり、データ処理と計算のために定数データ、セル参照、演算子、その他の要素と組み合わせられます。ユーザーは数式を使用して目的に応じた結果を計算するため、Excel の数式は値を返す必要があります (返すことしかできません)。
数式の構造: =(C2+D2)*5 数式の構造という観点から見ると、数式を構成する要素には通常、等号、定数、参照、演算子などの要素が含まれます。このうち、=記号は欠かせません。ただし、実際のアプリケーションでは、配列、Excel 関数、または名前 (名前付き数式) を使用して数式を操作することもできます。
通常、Excel は数式で複数の演算子が使用されている場合、同じレベルの演算子の優先順位に従って演算を左から右の順序で実行します。そして右側が逐次操作。具体的な優先順位は以下の通りです。
Excelの数式を使用して計算を行うと、何らかの理由で正しい結果が得られず、エラー値が返されることがあります。一般的なエラー値とその意味を以下の表に示します。
数式の結果がエラー値を返した場合は、エラーの原因を速やかに特定し、問題を解決するために数式を修正する必要があります。
Excel関数は通常、関数名、左括弧、パラメータ、半角カンマ、右括弧で構成されます。
関数式の構造: =IF(A1>0,"正の数", IF(A1<0,負の数,"")) 関数のパラメータは数値、日付、数値などの要素で構成できます。テキスト、または定数、配列、セル参照、またはその他の関数を使用できます。
関数のパラメーターが関数でもある場合、Excel ではそれを関数のネストと呼びます。データベース関数、日付と時刻関数、エンジニアリング関数、財務関数、情報関数、論理関数、クエリおよび参照関数、数学関数および三角関数、統計関数、テキスト関数、ユーザー定義関数を含む合計 11 種類の関数があります。 。
この記事の内容は、各機能が何をするのか、どの機能を使えばこの問題を解決できるのかなどをカタログ形式で紹介しています。具体的な使用方法はBaiduで学ぶことができます。
関数については、どのような関数を選択すればよいのか、必要なパラメータとその使い方を理解するだけで、丸暗記する必要はありません。たとえば、フィールドを選択したり、LEFT/RIGHT/MID 関数を使用したり...その他の詳細は万能の Baidu にお任せください。
以下では、これらの一般的に使用される必要な機能を、さまざまなアプリケーション シナリオに応じて分類して紹介します。
1. アソシエーションマッチングクラス
必要なデータが同じ Excel シートにない、または別の Excel シートにあるデータが多すぎると、コピーするのが面倒でエラーが発生しやすくなります。次の関数は、複数のテーブルの関連付けや行間の比較に使用されます。テーブルが複雑であればあるほど、使用するのが楽しくなります。
01.VLOOKUP
機能: 条件を満たす最初の列の要素を検索するために使用されます。
構文: =VLOOKUP (lookup_value,table_array,col_index_num, [range_lookup])
*備考: [ ] はオプションのパラメータ、残りは必須パラメータです。以下同様です。 =VLOOKUP (検索する項目、検索する場所、返される値を含む範囲内の列番号、近似一致または完全一致を返します - 1/TRUE または 0/FALSE として示されます)。例: セル F5 に名前がある従業員の役職をクエリします。
02.HLOOKUP
機能: テーブルまたは数値配列の最初の行の値を検索し、テーブルまたは配列の指定された行の列の値を返します。 HLOOKUPのHは「線」を表します。
構文: =HLOOKUP(lookup_value,table_array, row_index_num, [range_lookup])
例: =HLOOKUP("Axle",A1:C4, 2, TRUE) は、最初の行で車軸を検索し、同じ列 (列 A) の行 2 の値を返します。
LOOKUP と HLOOKUP の違い: 比較値がデータ テーブルの最初の行にある場合、指定した行数を下方向に表示する場合は、HLOOKUP を使用できます。 VLOOKUPは、比較値が検索対象データの左列にある場合に使用できます。
03.インデックス
機能: テーブルまたは範囲内の値または値への参照を返します。
構文: =INDEX(配列,行番号, [列番号])
例: =INDEX(B2:D11,3,3) は、範囲 A2:B3 の 3 行目と 3 列目の交点にある値です。
04.マッチ
機能: 指定された領域 (特定の行または列) 内の指定されたコンテンツの位置を返すために使用されます。
構文: =MATCH(lookup_value,lookup_array, [match_type])
例: =MATCH(41,B2:B5,0) セル範囲 B2:B5 内の値 41 の位置。
マッチタイプ：
1 または省略: MATCH は、lookup_value 以下の最大値を検索します。
0: MATCH を使用して、lookup_value と完全に等しい最初の値を見つけます。
-1: lookup_value 以上の最小値を見つけるために MATCH します。
05.ランク
機能: 特定の領域内の値のグループの中から特定の値のランキングを見つけます。
構文: =RANK(数値,参照,[順序])
例: =RANK(A3,A2:A6,1) 上表の A2:A6 における A3 の順位付け方法: 0 が降順、1 が昇順、デフォルトは 0
06.ROW
機能: 参照された行番号を返します。
構文: = ROW([参照])
例: = ROW() 数式が配置されている行の行番号
07.コラム
機能: セルが配置されている列を返します。
構文=COLUMN(参考)
例: =COLUMN (D10) は、列 D が 4 番目の列であるため、4 を返します。
08.オフセット
機能: 指定された行数と列数を持つセルまたはセル範囲への参照を返します。返される参照は、単一のセルまたはセルの範囲です。返す行数と列数を指定できます。
構文: =OFFSET(参照、行、列、[高さ]、[幅])
例: =OFFSET(D3,3,-2,1,1) はセル B6 の値を表示します。ここで、3 は下の 3 行、-2 は左側の 2 行、1 は行の高さと列の幅です。 。
データ処理の前に、文字列スペースのクリア、セルの結合、置換、文字列のインターセプト、文字列が出現する場所の検索など、抽出されたデータを最初にクリーンアップする必要があります。
インターセプト文字列: MID /LEFT/RIGHT を使用します
セル内の内容を置換します: SUBSTITUTE /REPLACE
セルを結合: CONCATENATE を使用します。
文字列スペースのクリア: TRIM/LTRIM/RTRIM を使用します。
セル内のテキストの位置を検索: FIND/SEARCH
09.ミッド
機能: 文字列を途中からインターセプトします
構文: =MID(text,start_num, num_chars)
例: =MID(A2,1,5) A2 の文字列の最初の文字から開始して 5 文字が返されます。
ID番号を元に年月を抽出します。
10.左
機能: 文字列を左からインターセプトします。
構文: =LEFT(text,[num_chars])
例: =LEFT(A2,4) 最初の文字列の最初の 4 文字。
11.右
機能: 文字列を右からインターセプトします。
構文: =RIGHT(text,[num_chars])
例: =RIGHT(A2,5) 最初の文字列の最後の 5 文字
12.交代
機能: テキスト文字列内の old_text を new_text に置き換えます。
構文: =SUBSTITUTE(テキスト,古いテキスト,新しいテキスト,[インスタンス番号])
例: =SUBSTITUTE(A2, "Sales", "Cost") は、"Sales" を "Cost" (コスト データ) に置き換え、電話番号の一部を置き換えます。
13.交換
機能: セル内の文字列を置換します。
構文: =REPLACE(old_text,start_num,num_chars,new_text)
例: =REPLACE(A2,6,5,"*") A2 の 6 文字目 (f) から 5 文字を 1 文字 * に置き換えます。
REPLACE と SUBSTITUTE の違い: 2 つの関数は非常に似ています。違いは、REPLACE は位置に応じて置換を実行し、どの位置から開始するか、置換の数、および置換後の新しいテキストを提供する必要があることです。 SUBSTITUTE はテキストの内容に応じて置き換えられ、古いテキストと新しいテキスト、どの古いテキストが置き換えられるかなどを指定する必要があります。したがって、REPLACE は固定位置のテキスト置換を実装し、SUBSTITUTE は固定位置のテキスト置換を実装します。
14. 連結する
機能: 2 つ以上のテキスト文字列を 1 つの文字列に連結します。
構文: =CONCATENATE(テキスト1,[テキスト2], ...)
セル内のコンテンツを結合するもう 1 つの方法は & です。結合するコンテンツが多すぎる場合は、CONCATENATE の方が効率的です。
例: =CONCATENATE(B2, " ", C2) は、セル B2 の文字列、スペース文字、セル C2 の値の 3 つの部分を結合します。
15.トリム
機能: 単語間の 1 つのスペースを除く、テキスト内のすべてのスペースを削除します。
構文: =TRIM(テキスト)
Text はスペースを削除するテキストです。
例: =TRIM("First Quarter Earnings ") は、数式のテキストから先頭と末尾のスペースを削除します。
16.LTRIM
機能: 文字列の左側からスペースまたはその他の事前定義された文字を削除します。
構文: =LTRIM (文字列, [文字リスト])
17.RTRIM
機能: 文字列の右側からスペースまたはその他の事前定義された文字を削除します。
構文: = LTRIM(文字列, [文字リスト])
18.探す
機能: テキスト位置の検索
構文: =FIND(find_text,within_text,[start_num])
例: =FIND("M",A2) セル A2 の最初の "M" の位置
19.検索
機能: 左から右に検索して、指定した文字またはテキスト文字列が文字列内で最初に出現する位置を返します。
構文: =SEARCH(find_text,within_text,[start_num])
例: =SEARCH("e",A2,6) セル A2 の文字列の、6 番目の位置から始まる最初の "e" の位置。
FIND と SEARCH の違い: これら 2 つの関数の機能はほぼ同じであり、文字の位置を検索できる点が異なります。FIND 関数は大文字と小文字を区別して検索しますが、SEARCH 関数は曖昧に検索します。大文字と小文字を区別。
20.レン
機能: テキスト文字列の文字数を返します。
構文: =LEN(テキスト)
例: =LEN(A1) セル A1 の文字列の長さ
21.レンブ
機能: テキスト文字列内の文字を表すために使用されるバイト数を返します。
構文: =LENB(テキスト)
例: =LEN(A1)セル文字列 A1 のバイト数。
ロジックは、名前が示すように、詳細には触れず、機能のみを説明します。
22. もしも
機能: 論理関数 IF 関数を使用する場合、条件が true の場合、関数は値を返し、条件が false の場合、関数は別の値を返します。
構文: =IF(論理,真の場合の値,偽の場合の値)
IF 関数は、指定された条件が true と評価された場合は値を返し、条件が false と評価された場合は別の値を返します。
23.カウンティフ
機能: 特定の条件を満たすセルの数をカウントするために使用されます。たとえば、顧客リストに特定の都市が表示された回数をカウントします。
構文: =COUNTIF(セル1:セル2、条件)
特定の店舗がリストに表示された回数をカウントします。
24.そして
機能：論理的判断、「和集合」に相当。
構文: すべてのパラメータが True の場合、True が返されます。複数条件の判定によく使用されます。
例: =AND(A2>1,A2<100) A2 が 1 より大きく 100 未満の場合は TRUE が表示され、それ以外の場合は FALSE が表示されます。
25.OR
機能：論理的判断、「or」に相当。
構文: パラメータが True である限り、Ture が返され、複数条件の判定によく使用されます。
例: =OR(A2>1,A2<100) A2 が 1 より大きいか 100 より小さい場合は TRUE が表示され、それ以外の場合は FALSE が表示されます。
Excel のテーブル統計を使用する場合、多くの場合、Excel に付属するさまざまな数式を使用する必要があります。これは最も一般的に使用されるタイプでもあります。 （これらについては、Excelにはショートカット機能が付属しています）
26.分
機能: 特定の領域の最小値を見つけます。
構文: =MIN(数値1, [数値2], ...)
例: =MIN(D2:D11) D2:D11 の範囲内の最小数値。
27.MAX
機能: 特定の領域の最大値を見つけます。
構文: =MAX(数値1, [数値2], ...)
例：=MAX(A2:A6) A2:A6 領域の最大値。
28.平均
機能: 特定の領域の平均値を計算します。
構文: =AVERAGE(数値1, [数値2], ...)
例: =AVERAGE(D2:D11) セル範囲 D2 ～ D11 の数値の平均。
29.カウント
機能: 数字が入っているセルの数を数えます。
構文: =COUNT(値1, [値2], ...)
例: =COUNT(A2:A7) セル範囲 A2 ～ A7 内の数字が含まれるセルの数をカウントします。
30.カウンティフ
機能: 指定された一連の条件で指定されたセルの数をカウントします。
構文: COUNTIFS(基準範囲1,基準1,[基準範囲2,基準2],…)
例: =COUNTIFS(A2:A7,"<6",A2:A7,">1") は、セル A2 ～ A7 に 1 ～ 6 の数値 (1 と 6 を除く) がいくつ含まれるかを計算します。
31.サム
機能: セル範囲内のすべての値の合計を計算します。
構文: =SUM(セル 1:セル 2)
例: =SUM(A2:A10) はセル A2:10 の値を加算します。
32.スミフ
機能: 条件を満たすセルの合計を求めます。
構文: =SUMIF(範囲,基準, [合計範囲])
例: =SUMIF(A2:A7,"フルーツ",C2:C7) 「フルーツ」カテゴリのすべての食品の売上の合計。
32.スミフス
機能: 指定された条件を満たすセルのグループを合計します。
構文: =SUMIFS(合計範囲,基準範囲1,基準1,[基準範囲2,基準2],...)
例： =SUMIFS(A2:A9, B2:B9, "=香*", C2:C9, "LUNING") 「LUNING」が販売する「香」で始まる商品の合計数量を計算します。
33.サムプロダクト
機能: 対応する配列積または面積積の合計を返します。
構文: =SUMPRODUCT (配列 1, [配列 2], [配列 3], ...)
例: =SUMPRODUCT(Table1!A1:Table1!A100,Table2!B1:Table2!B100) 表1のA1～A100と表2のB1～B100の積の和、つまりA1*B1+A2を計算します。 *B2+A3* B3+…
34.STDEV
機能: サンプルに基づいて標準偏差を推定します。
構文: STDEV(数値1,[数値2],...)
例: =STDEV(D2:D17) カラムの標準偏差
35.小計
機能: リストまたはデータベースの小計を返します。
構文: =SUBTOTAL(関数番号,ref1,[ref2],...)
例: =SUBTOTAL(9,A2:A5) は、最初のパラメーターとして 9 を使用して、セル A2:A5 の小計値の合計を計算します。
http://36.INT／ROUND
機能: ROUND 関数は、数値を指定された桁数に丸めます。
構文: =ROUND(A1, 2)
例: =ROUND(2.15, 1) は、2.15 を小数点第 1 位に四捨五入します。
機能: INT は、数値を最も近い整数に切り捨てます。
構文:=INT(8.9) 8.9 を最も近い整数に切り捨てます。
時刻の形式と変換を処理するために特別に設計されています。
37.今日
機能: 現在の日付のシリアル番号を返します。
構文: =TODAY()
li'z=TODAY()+5 は、現在の日付に 5 日を加えたものを返します。たとえば、現在の日付が 2012 年 1 月 1 日の場合、この数式は 2012 年 1 月 6 日を返します。
38.今
機能: 現在の日付と時刻のシリアル番号を返します。
構文: =Now()
=NOW()+7 は 7 日後の日付と時刻を返します。
39.年
機能: 特定の日付に対応する年を返します。
構文: =YEAR(シリアル番号)
=YEAR(A3) セル A3 の日付の年
40.月
機能: 日付の月を返します。
構文: =MONTH(シリアル番号)
=MONTH(A2) セル A2 の日付の月
41.DAY
機能: 日付の日数をシリアル番号で返します。
構文: =DAY(シリアル番号)
=DAY(A2) セル A2 の日付の日数
42.平日
機能: 特定の日付に対応する曜日を返します。デフォルトでは、日数は 1 (日曜日) から 7 (土曜日) までの範囲の整数です。
構文: =WEEKDAY(シリアル番号,[戻り値の種類])
=WEEKDAY(A2) 1(日)～7(土)の曜日
=WEEKDAY(A2, 2) 1(月曜日)から7(日曜日)までの曜日。
43.DATEIF
機能: 2 つの日付の間の日数、月数、または年数を計算します。
構文: =DATEDIF(開始日,終了日,単位)
=DATEDIF(開始日,終了日,"Y")期間の年数
=DATEDIF(開始日,終了日,"D")期間の日数
=DATEDIF(Start_date,End_date,"YD") は、日付の年と期間の日数を無視します。
添付：
