Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : Excel 2016)
数式の解説をお願いします
投稿日時: 19/07/17 17:34:42
投稿者: vaioyuki

いつもお世話になっております。
今回は関数の解読?を教えていただきたいです。
 
シートは、
[メイン][営業][工場][1]〜[12]
全部で15シートあります。
[メイン]シートにはマクロのボタンのみです。
[営業][工場]シートは表の作りは同じでこちらに関数が入力されています。
[1]〜[12]は月になります。
[営業][工場]シートは1行目には表題があり、
A列〜F列までは2行目と3行目が結合されていてタイトル項目があり、
G列〜BZ列までは3行目に1月〜12月の入力がしていたりして2行目は各々のタイトル項目が入力されています。
(今回はG列以降は関係ありませんので省略します)
[1]〜[12]シートは1行目にタイトルがあり2行目からデータが入っています。
 
 
※D列E列については空白でありタイトルが守秘義務にひっかかりますので伏せさせてください
 
[営業]シート
         7月報告書
 A  |  B  |  C    | D  | E  |  F   |
 No. | 部署 | 電話番号 | ● | △ | 所有者 | 
 5  | ■■ | 0000-0000 |   |   | ○一郎 |
 
[1]シート
 A     |  B  | C   |  D  | E  |  F  |  G    |〜略〜|  Z   |
 @     | @CD | A  | ACD | B | BCD | 電話番号  |〜略〜| 使用者 | 
 本部   | 10  | 工場 | 1   |   |    | 0000-0001 |   | ○次郎 |
 事業部  | 75  | 工場 | 1   |   |    | 0000-0001 |   | ○三郎 |
 センター | 40  | 工場 | 1   |   |    | 0000-0001 |   | ○史郎 |
 ■■   | 20  | 営業 | 1   |   |    | 0000-0000 |   | ○一郎 |
 
 
@A4に入っている関数
=MATCH("営業*",INDIRECT("'"&$A$2&"'!R"&A3+1&"C3:R"&$A$200&"C3",FALSE),0)+A3
 
AB4に入っている関数
=TRIM(OFFSET(INDIRECT("'"&$A$2&"'!R1C1",FALSE),$A4-1,0))
 
C列F列はAのOFFSET関数の列数の数値が違うだけです。
 
A2にはVBAで指定した月数(5月)を自動入力し、ユーザー定義で No. にしているようです。
A200には208の数字が入力されています。
そうなるとA2とA3は結合されているので@のINDIRECT関数の参照文字列は'5'!R6C3:R208C3
+A3はどうして?
と頭の中がぐちゃぐちゃになりました。(^^;)
 
私の解析も間違っているかもしれないと思い、相談させていただきました。
勝手ながら終業時間が近づきお礼などは明日になるかもしれませんが、
よろしくお願いします。
 
 

回答
投稿日時: 19/07/17 18:31:25
投稿者: WinArrow
投稿者のウェブサイトに移動

この表の数式は、誰が作成したんですか?
作成者に聞いてみましたか?
 
 
とはいえど・・・・
 
 
まず
>A4に入っている関数
と書かれているが、表の中では、どこなのかわかりません。
 
ので、
数式の一般的な説明はHELPで勉強してください。
 
MATCH("営業*",INDIRECT("'"&$A$2&"'!R"&A3+1&"C3:R"&$A$200&"C3",FALSE),0)+A3
         <------------------------------------>
<----〜〜----> は、INDIRECT関数の引数なので、文字列になります。
個々だけ取り出して、どのような文字列になるか、確認してみましょう。
 

回答
投稿日時: 19/07/17 22:10:57
投稿者: んなっと

"1"シート
少しだけ式を変えると...
 
"1"シート
 
      A   B   C   D     E   F
 1    @ @CD  A ACD     B BCD
 2   本部  10 工場   1 0000-0001  あ
 3  事業部  75 工場   1 0000-0002  い
 4 センター  40 工場   1 0000-0003  う
 5   ■■  20 営業   1 0000-0004  え
 6   本部  10 工場   1 0000-0005  お
 7  事業部  75 工場   1 0000-0006  か
 8   △△  21 営業   2 0000-0007  き
 9  事業部  75 工場   1 0000-0008  く
10   □□  22 営業   3 0000-0009  け
 
"営業"シート
 
   A   B     C  D
1 No. 部署  電話番号 ●
2  1           
3  1           
4  5 ■■ 0000-0004 え
5  8 △△ 0000-0007 き
6  10 □□ 0000-0009 け
 
A4
=MATCH("営業*",INDIRECT("'"&$A$2&"'!C"&A3+1&":C"&$A$200),0)+A3
下方向・↓
 
※意味:
"1"シートのC列で、以下の条件で検索したセルの行番号。
条件:すぐ上のA3の数字1に1をたした2行目から下を調べて、最初に見つかった"営業*"。
 
B4
=TRIM(OFFSET(INDIRECT("'"&$A$2&"'!A1"),$A4-1,0))
下方向・↓
C4
=TRIM(OFFSET(INDIRECT("'"&$A$2&"'!E1"),$A4-1,0))
下方向・↓
D4
=TRIM(OFFSET(INDIRECT("'"&$A$2&"'!F1"),$A4-1,0))
下方向・↓
 
この際、R1C1形式にも慣れておくのもいいかもしれません。

投稿日時: 19/07/18 11:01:38
投稿者: vaioyuki

WinArrowさま
 
ありがとうございます。
これを作成した前任者が辞めてしまい、VBAのスレッドでも教えていただいたりしたのですがこちらのファイルもVBAで簡素化できないものかと思い関数の分析?を始めております。(^^;)
 
 
| A  |  B  |  C    | D  | E  |  F   |
1|         7月報告書
2|
3| No. | 部署 | 電話番号 | ● | △ | 所有者 |
4| 5  | ■■ | 0000-0000 |   |   | ○一郎 |
 
説明足らずで申し訳ありません。
太字部分が行列番号になります。
 
ひとつずつばらしてみた結果、意味が分かってきました。
ありがとうございました。
 
 
んなっとさま
 
ありがとうございます。
ひとつひとつ解明していくとわかりました!!
+の意味も解明出来ました!!
MATCH関数の何を出してきて何を足されているのかが不明だったのですがスッキリしました。
 
ありがとうございました。