Excel公式

Excel公式

• cell内容比较
  IF(A1=B1, "0", "1")
  同效果：=A1=B1
  　　　　=NOT(A1=B1)*1

• 时间型变换表示

dim a, b
a="17: 1: 2"
b=Format(a, "hh:mm:ss") 　　⇒17:01:02
b=Format(a, "hh時mm分ss秒") ⇒17時01分02秒

• 统计只表示出来的内容，用subtotal
  例如：フィルターで表示されるセル
  構文：SUBTOTAL(集計方法, 参照1,[参照2],...)
  =subtotal(9, E2:E15)

• 同时满足多个条件，可以用AND函数实现。
  構文：AND(論理式1,[論理式2],[論理式3],...)
  =AND(1, 2, 3)　　　⇒TRUE
  =IF(AND(COUNTIF(B2:C4,"Excel"), COUNTIF(B10:C12, "実績時間")), "○", "ー")

• 複数条件をOR条件で合計する方法
  SUMIF関数
  =sum(sumifs(D3:D11, C3:C11, {"リンゴ", "バナナ"}))
  　　　　　　合計範囲　条件範囲　配列の形式で条件指定

• 判断某行数据在前面的一览数据中是否存在
  SUMPRODUCT関数
  =IF($I13="", "", SUMPRODUCT(($I13=$A:$A)($J13=$B:$B)($K13=$C:$C)=1))

　　VLOOKUP　　　选择一个值
　　SUMPRODUCT 　能选择多个值

• SUMPRODUCT複数条件
  SUMPRODUCT関数で複数条件の合計・集計が便利
  条件が一つの場合
  =SUMRODUCT((D2:D13="売上")1, E2:E13)
  複数条件の場合
  =SUMPRODUCT((C2:C13="A")(D2:D13="売上"), E2*E13)
  対してSUMIFS関数だと、
  =SUMIFS(E2:E13, C2:C13, "A", D2:D13, "売上")
  ※SUMIFS関数は「他ブック参照でエラーが出る」

• ADDRESS和INDIRECT组合使用
  先用ADDRESS拼接出表示统计范围的字符串
  然后，再用INDIRECT对字符串表示的范围进行间接引用。
  eg：统计第一个处理的测试件数。后续处理用第一个公式拷贝粘贴即可。
  =SUM(INDIRECT(ADDRESS(ROW(), 2) & ":" & ADDRESS(ROW()+28, 2)))
  式样书名=mid(C1, find("式", C1, 1), 10)

• 跨sheet取得指定单元格的值 INDIRECT

　　=INDIRECT(B2&"!F1")
　　B2是sheet名所在的单元格，F1是要取得的各个sheet的指定单元格。

　　跨sheet取得ADDRESS拼接出来的单元格内容
　　根据B列单元格记载的行号，从别的sheet取得A列对应行号的cell情报。
　　=B3　　　　　　　　　　　　　　　　　257
　　=ADDRESS(B3,1)　　　　　　　　　　　 $A$257
　　="例文!"& ADDRESS(B3,1)　　　　　　　 例文!$A$257　　　　　 拼接处跨sheet的字符串
　　=INDIRECT("例文!"& ADDRESS(B3,1))　　お待たせしました。　 　取得具体内容（使用INDIRECT）

• EXCEL関数で空白以外のセルを数える方法
  １．COUNTIF関数
  =countif(C3:C8, "<>")
  検索条件を、"<>"と入力して、空白以外のセルを抽出します。

　　２．COUNTA関数で空白以外のセルを数える
　　=COUNTA(C3:C8)
　　３．COUNTIF関数でワイルドカードを使う
　　=COUNTIF(C3:C8,”*”)
　　「*」は文字列しか認識しないので、数字を除く

• SUBSTITUTE関数の使い方
  =SUBSTITUTE(E3, "/", "●", LEN(E3)-LEN(SUBSTITUTE(E3, "/", "")))
  =FIND("●",F3, 1)
  =LEFT(G3, F3-1)