Excel問題 やってみようよ
こんにちは😇
ここ2-3日?で一部いろんな意味で盛り上がっていましたExcelを使った問題。
こちらに用意しました。
使えなかったら教えてください。
https://1drv.ms/f/s!AjhE_-FAVm7pgd45iT7xBL1jsbZkaQ?e=ShqaF6
教育、学習目的です。他に意味は無いので、それ以外の書き込みは遠慮していただきたいです。
元の掲示板はまだあるようですが、リスタートしたいので新しく立てています。
元のスレ主さん。良かったら私の解答も見てみてね。
Excel達人じゃないので、普通の式です。
h29が元の問題。
yDaemonが私の解答です。模範解答があるのかは不明ですが、いらないでしょ😁 縛られたくないし。
グラフはやっていません。とりあえず式設定だけ。
なんでそんな式を?!というところもあると思います。後にコメントしたいです。
自分でやってみるも良し。
ちらっと眺めるもよし。
お仕事でエクセルを使うけど、式入力が良く分からんという人は、ヒントになるかもしれません。
児童、生徒、学生さんで、働いていないとこういうのってピンとこないことが多いです。でも、高校の授業でやると聞いたことがあるので、参考になれば、と思います。私の時はFortranでした。今思えば簡単なのにちんぷんかんぷんでした。
何か感想とか、こんな計算方法もあるよ!みたいなお話を聞けると嬉しいです。
この問題。エクセルで「コード」が出てきます。識別するためのコードね。
「番号なんでしょ」っていうことは知っていると思います。何で番号なのか?
この問題に出てくるんですけど、例えば「米」。
米、こめ、コメ、お米、とかいろんな言葉(文字列)があります。人が見たら同じことと認識しやすいですが、コンピュータだと別々に認識されやすいです。うまいこと処理できるかもしれませんが、計算に時間がかかったり、ホントは違ったりするかもしれません。
そこで、「コード」を作って管理します。これがコード管理の目的です。
コード0101=米 とか決めます。0101=麦 は登録できないようにします。(やれないこともないですが、管理がおかしくなりやすい)。
会社だと分かりやすいかも。
オプテージ と 株式会社オプテージ は同じなのに入力する人によってバラバラになると管理しにくくなります。
9800=株式会社オプテージ として、入力時はコード9800を使います。そうやって同じものがバラバラにならないようにします。
すっきりしていそうですが、このコードも管理しないと、米=0101にしたのに、誰かが未登録と思って米=0202を追加するかもしれません。だからコードの管理も必要です。
とりあえず、お暇なら見てよね😇
最初にやったとき、コードからXLOOKUP関数で資料シートのデータを持ってきていました。そうすると、資料シートから選択範囲を年によって変えなければなりません。表はそこそこの大きさなのでちょっと面倒でした。ただし、会社の業務でXLOOKUPやVLOOKUP関数を使う方法は一般的です。覚えておいたほうが良いです。
ここでは、B列の対象の類別コードが資料シートのどこにあるかを探します。5行目の対象年が資料シートのどこにあるかを探します。INDEX関数を使って、何年のこのコードの値を取り出します。これで、年ごとに参照する範囲を変更せずに、全セルでコピペすることができました。
=INDEX(資料!$D$6:$BG$70,XMATCH(表計算問題!$B6,資料!$A$6:$A$70),XMATCH(表計算問題!D$5,資料!$D$4:$BG$4))
ちなみにINDEX関数とXMATCH関数は初めて使いました。ネットで調べてヘルプで確認しました。チャット系AIだとすぐに教えてくれるのかもしれません。知らんけど。
実務では、式を入れずにコピペする人も結構いる気がします。
=INDEX($D$6:$I$20,XMATCH($C32,$C$6:$C$20),XMATCH(E$31,$D$5:$I$5))
表2もINDEX関数を使いました。ここはXLOOKUP(VLOOKUP)でも良さそうです。なんでINDEXにしたか?
理由1 初めて使って面白いので使ってしまった
理由2 比較対象を2015年から変えたときに便利だと思った
でも、どうせ表1の熱量順位を使わなきゃいけないんだから、それなら表1の2015年を対象年として決めれば、そこの年を変更すれば良いのだから、それをアテにしてLOOKUP系で良かった。だって、2014年にしても表1には値が無いから、2015を書き換えなきゃいけない。E31を=I5にして処理するほうが便利ですね。
表3でも、いろんな年の比較を想定してINDEXにしたんだけど、表1のD列とI列を使って比較すると決めて、その2列を使ってLOOKUPでデータ計算が良かった気がする。
増減でマイナスのときは△を付ける、のはIF関数を使いました。
セル書式設定で、マイナスは△があるんだけど、数字も出ちゃうのでうまくいきませんでした。
業務ではマイナスは△というのは良くあるみたいなのですが、科学系ではやらないので、まだ馴染めません。麻雀でマイナスのときも△使います。
総量の増減の式を表示して、という問題。ストレートに、その値のセル同士の引き算にしました。これ、なんでか?というと、穀類などはコードで管理されていました。総量はもともとコードがありません。表3になって急に1700なるコードが付きました。
コード0100ー1600まではコードで拾って計算しました。総量はコードが無いこと、あと「総量」なる種別で検索して値を取り出して計算するのはあんまり好みじゃなかったのです。で、確実に見てわかるセルを選択しての計算式にしました。まあ、表1の総量にコード1700を入れても良かったかもしれません。でも、最初にコード設定が無い表にコードを入れるのは何かなー?と思いました。
データベースなら確実にそうするのになぁ。
と思ってエクセルの資料シートを見てみると、こっちは式参照どころか直打ち😅
年の上に、1,2,3,4,・・・とあるのは、初めの年を1として順につけている。
年の下にも数字がある。こっちは35からスタート。63の次は1。なんだこれ?と思ったら、昭和と平成😅
あれって、コピペするときの効率が良くなるためであって、式自体は同じ計算になります。
=A1+B2
も
=$A1+$B$2
も計算結果は同じです。
コピペしたときに、$が無い部分がずれてコピーされるか、$付きが変化しないかでしかありません。
私の場合ですけど、実際に相対参照でも絶対参照でも、2~3か所にコピペしたあと、式と結果が期待通りになっているかをチェックしてから他のセルにコピペします。結構間違っていることが多いです。
悩んでいるよりも、コピペしてみて、間違いを直して、良い式になってから他のセルにコピペしたほうが作業は速いと感じます。
>選択範囲を年によって変えなければなりません。
これは
vloookup関数の列数を指定する引数に
列を探すmatch関数を入れる方法もありますね。
具体的には
=VLOOKUP($B6,資料!$A:$BG,3+MATCH(D$5,資料!$D$4:$BG$4,0),FALSE)
---
必ず1年ごとに並んでいるなら、match関数無くても
D$5‐1959(年)+3(タイトル列数)
を列番号にする方法もありますね。
ここ見るとxlookupのネストでも簡単にできるようです。
https://support.microsoft.com/ja-jp/office/xlookup-関数-b7fd680e-6d10-43e6-84f9-88eae8bf5929
>> さと さん
元ネタあるんですけど、ちょっと変な荒れ方をしているので分けました😅=XLOOKUP($B6,資料!$A$6:$A$70,XLOOKUP(D$5,資料!$D$4:$BG$4,資料!$D$6:$BG$70))
>> Y. Daemon@ポリアモラス さん
どこにあるんだろう、気になります>> さと さん
ありがとうございます。表計算問題 セルD6に
=XLOOKUP($B6,資料!$A$6:$A$70,OFFSET(資料!$D$6:$D$70,0,XLOOKUP(D$5,資料!$D$4:$BG$4,資料!$D$3:$BG$3)-1))
これで出来ました😇
XLOOKUPのネストとOFFSET。慣れれば簡単ですね。
>> Y. Daemon@ポリアモラス さん
あれ、ちょっと違う。。。OFFSET不要?
>> さと さん
教えてください! みたいなのです。軽く眺める程度で。。。>> さと さん
XLOOKUP関数って、単独セル値だけじゃなくて、セル範囲で返せるんですね。スッキリ書けます😊今後も同じような処理をする可能性がある場合は、関数を調べながら、式を作ります。
>> Y. Daemon@ポリアモラス さん
OFFSET使って簡単にする式として、下記でも出来ます。(1960年から1年ずつ昇順に並んでいるので単純な引き算で)
=XLOOKUP($B6,資料!$A$6:$A$70,OFFSET(資料!$D$6:$D$70,0,D$5-1960))
>> Y. Daemon@ポリアモラス さん
XLOOKUP関数って、セル範囲で返るんですね。でも個人的な好みとしては、matchとINDEXの組み合わせの方が好きですね。なぜなら、メモリーを余り使わなそうだから。
>> marumaru218 さん
フィルタ機能で作る方法もありますね。>> marumaru218 さん
こんにちは😃Excelって、たくさんの関数がありますけど、結局はのところ、自分がどれを上手いこと使えるか、というところなので、絶対の正解もないですよね。
よく分からない関数を駆使しても、合っているのかどうか分からなきゃ意味ないし。
今回の件で、私は新しくいくつかの関数を使えるようになりました。いろいろありましたが、良かったです😇
>> さと さん
実務ではフィルタ機能は感覚的に使いやすいですね。見えるもの、見えないものって分かりやすいです。上手いこと、関数を駆使したテンプレートを作っておけば、とても効率的に思えます。
過去データを参考にしてコピーを修正する方法は、昔のデータを消し忘れたり、大事なことをやり忘れたりすることがときどきあります。そういうところも直していければなぁ、と思っています。
>> さと さん
OFFSET ありがとうございます😊今回、使い方を覚えました。いろんなシーンで使えそうです。
>> じんで さん
ありがとうございます😊現実で上手い式をすぐに書けない場合には、力業は効果的ですよね。そのへんのバランス感覚はコンピュータを使う時に大事だと思います。
どういう結果になるか試していませんが、メニューのデータから、ソート機能もあります。
>> Y. Daemon@ポリアモラス さん
そうそう、メニューの並び替えを使いました。コピペしたら表1を再びコードで並び替えて表復元。