[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『優先順位の条件付け』（冬将軍）
お世話になります。
表入力を効率化したくてご相談させていただきます。
A、B列に一ヶ月の日付、曜日、C列以降にグループ名(以下表内はGPとします)、部署、氏名が入ります。グループはAからGの7つで、氏名は4人いれます。
A B C D E F G H I J K L M N 日付 曜日 GP 部署 氏名 GP 部署 氏名 GP 部署 氏名 GP 部署 氏名
いつも部署ごとに名簿が送られてきて、届き次第その日付にグループ、部署、氏名を入力してます。
入力するときの判断条件として、
(1)女性(赤字の人)は左に詰めて入力する。 (1)グループがAに近い人を左に持っていく。(Gに近い人を右に持っていく)
この二つがあります。
先程も書きましたが部署ごと、届く順番に入力していくので、最初はいいんですが後半になると頻繁に氏名の入れ換えをすることになります。
この作業をなくすために、同じ表シートを用意して左から順番に入力していくと完成シートでは並び替えられた状態で入力される。
そのようなことを考えたのですが、どのような関数を使えば出来るのかさっぱり分かりません。(そもそも関数で赤字判定とか出来るのでしょうか)
ご教授お願いします。
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
「部署ごとに名簿が送られてきて」とのことですが、何で送られてくるのですか。
紙ですか。エクセルファイルですか。・・・・
（hatena） 2018/12/02(日) 09:33
全部署のデータが揃ってから入力しない理由はあるのでしょうか。
最後に、性別とGPで並べ替えてからコピペを繰り返せば
入れ替えすることはなくなると思いますが？
（マナ） 2018/12/02(日) 10:26
マナさん、名簿がくる時期は部署ごとバラバラで、月の半ばからひどいときは前日(31日)に届くこともあります。
なので揃ってからまとめて、という方法は難しいです。すみません。
（冬将軍） 2018/12/02(日) 11:38
でしたら、31日まで、並べ替えの可能性があるということですよね。
なぜ、31日にデータが揃った段階で、まとめて入力しないのでしょうか。
（マナ） 2018/12/02(日) 11:52
＞名簿は紙で送られてきます。女性かどうか氏名を見て私たちが赤文字にしてます。 ＞(判断出来なければ部署に直接聞いてます)
そうなると、女性の判別データは、こちら側でなんとでもなるので、 「同じ表シートを用意して左から順番に入力していく」時に、 女性の氏名の後ろに「#」とか、識別記号を付加することにしてもいいですよね？
何せ、文字の色を判別するのは数式では難しいので・・(マクロなら可能）
勿論、正規の表に自動的に並べる時には、#を消去して赤字で表示するように塩梅します(※)。 ※条件付き書式を利用することになります
一つ確認したいのですが、 同一日に同じグループの人が２人以上になることはありますか？
（半平太） 2018/12/02(日) 12:40
半平太さん、同じグループから複数入ることはあります。そのときの順番は先に入力した方から左に詰めてます。
（冬将軍） 2018/12/02(日) 12:48
＞同じグループから複数入ることはあります。
そうなると、女男順＞グループ順＞先に書いた順 ですね。
１．下図のレイアウトとします。
２．女性名の後ろに「#」を付加するものとします（例、下図V2セル、AB2セル、Y4セル）
３．数式
(1) C2セル =IFERROR(SUBSTITUTE(INDEX(2:2,MOD(AGGREGATE(15,6,(20000-(RIGHT($S2:$AB2)="#")*10000+CODE(UPPER(ASC($Q2:$Z2)))*100)+{17,"","",20,"","",23,"","",26},INT((COLUMN(B1)+1)/3)),100)+MOD(COLUMN(A1)-1,3)),"#",""),"")
これをN2セルまでコピー
(2) P2セル =A2
(3) C2:P2を下にコピー(必要範囲まで）
<結果図> 行 ____A____ __B__ _C_ __D__ __E__ _F_ __G__ __H__ _I_ __J__ __K__ _L_ __M__ __N__ _O_ ______P______ _Q_ __R__ __S__ _T_ __U__ __V__ _W_ __X__ __Y__ _Z_ _AA_ _AB_ 1 日付 曜日 GP 部署 氏名 GP 部署 氏名 GP 部署 氏名 GP 部署 氏名 日付 GP 部署 氏名 GP 部署 氏名 GP 部署 氏名 GP 部署 氏名 2 2018/12/1 C 部7 氏5 C 部1 氏9 D 部2 氏5 G 部2 氏6 2018/12/1(土) D 部2 氏5 C 部7 氏5# G 部2 氏6 C 部1 氏9# 3 2018/12/2 A 部6 氏5 B 部2 氏1 D 部2 氏7 E 部2 氏9 2018/12/2(日) D 部2 氏7 A 部6 氏5 E 部2 氏9 B 部2 氏1 4 2018/12/3 F 部8 氏1 A 部6 氏9 G 部1 氏8 2018/12/3(月) G 部1 氏8 A 部6 氏9 F 部8 氏1# 5 2018/12/4 C 部1 氏1 D 部4 氏8 F 部4 氏7 G 部9 氏4 2018/12/4(火) F 部4 氏7 G 部9 氏4 D 部4 氏8 C 部1 氏1
４．条件付き書式 E2以下必要範囲下まで選択して、 条件式 → =MATCH(C2&D2&E2&"#",$Q2:$Z2&$R2:$AA2&$S2:$AB2,0) ：文字を赤にセット
E2以下の条件付き書式を設定した範囲の書式をコピーして、「H2以下、K2以下、N2以下」に書式をペースト
（半平太） 2018/12/02(日) 14:23
＞グループはAからGの7つで
グループ名は仮称なのですが、説明時に"仮に"という言葉をいれてませんでした。完全に勘違いです。見直してみるとグループ名はAからGと断言してました。
この数式は高度過ぎてほとんど理解していないのですが、アルファベットや数字を昇順降順に並び替えてるのでしょうか？
だとすると、任意の文字列だと完全に違う数式になると思います。
もう少し自分で考えようと思います。
貴重な時間ありがとうございます。
（冬将軍） 2018/12/02(日) 15:09
＞この数式は？入力シートと反映シートと言う風にシートを分けることはできないのでしょうか。
出来ますよ。 ただ、セルの列位置が違って来るので、それなりに修正が必要になります。
また、参照範囲の頭にシート名も必要になってくるので ひとつの数式でやると更に長ったらしくなります。
※まぁ、ひとつの数式でやる事もないんですけど・・
あと、私は興味がなくなったのでドロップアウトしますが、 グループ名がアルファベットじゃないなら、 どんな順にすべきなのか説明されないと誰も作式できません。
これに相当する説明です ↓ ＞(1)グループがAに近い人を左に持っていく。(Gに近い人を右に持っていく)
（半平太） 2018/12/02(日) 19:25
＞ただ、セルの列位置が違って来るので、
{}の中の数字を変えたら出来ました。ありがとうございます。
＞グループ名がアルファベットじゃないなら、
"A"、"B"という文字列という認識で説明してました。
アルファベットだと順番の説明がわかりやいと思い使ったのですが、私の説明不足のせいで裏目に出てしまいました。
ありがとうございました。
（冬将軍） 2018/12/02(日) 19:56
結局、並び順の問題は
1、グループ名で入力。 2、別シートでグループ名をアルファベットに変換。 3、それを別シートに半平太さんの数式で並び替え。 4、別シートでアルファベットをグループに変換。
このように強引なちからわざで解決しました。数式はやはり自力ではいじれなかったです。
新たに質問ですが、最初に"#"で女性と表記した人のグループ、部署、氏名を赤字にしたいのですが、方法がわかりません。
最初の"#"は最終的に消えてしまうので、何に紐付けをすればいいのか思い浮かばず、、、
おそらく回答者を指名することはマナー違反だと思いますが、半平太さんへの質問になってます。時間があればよろしくお願いします。
（冬将軍） 2018/12/21(金) 21:28
＞ 1、グループ名で入力。 ＞ 2、別シートでグループ名をアルファベットに変換。 ＞ 3、それを別シートに半平太さんの数式で並び替え。 ＞ 4、別シートでアルファベットをグループに変換。
自力で切り拓いていく対応力は評価します。
・・・けど、いくらなんでも無駄すぎます(-_-;)
別シートにグループ名のリスト一覧を縦方向に作り、その範囲を「組名」と名前定義するものとします。
C2セル =IFERROR(SUBSTITUTE(INDEX(Sheet2!2:2,MOD(AGGREGATE(15,6,20000-(RIGHT(Sheet2!$D2:$M2)="#")*10000+MATCH(Sheet2!$B2:$M2,組名,0)*100+{2,0,0,5,0,0,8,0,0,11,0,0},FLOOR(COLUMN(C1)/3,1)),100)+MOD(COLUMN(A2)-1,3)),"#",""),"")
<Sheet1 結果図> 行 ____A____ __B__ _C_ __D__ __E__ _F_ __G__ __H__ _I_ __J__ __K__ _L_ __M__ __N__ 1 日付 曜日 GP 部署 氏名 GP 部署 氏名 GP 部署 氏名 GP 部署 氏名 2 2018/12/1 C 部7 氏5 E 部1 氏9 D 部2 氏5 G 部2 氏6 3 2018/12/2 B 部2 氏1 D 部2 氏7 E 部2 氏9 G 部6 氏5 4 2018/12/3 F 部8 氏1 A 部6 氏9 G 部1 氏8 5 2018/12/4 A 部9 氏4 F 部4 氏7 C 部1 氏1 D 部4 氏8
<Sheet2 サンプルデータ> 行 ______A______ _B_ __C__ __D__ _E_ __F__ __G__ _H_ __I__ __J__ _K_ __L__ __M__ 1 日付 GP 部署 氏名 GP 部署 氏名 GP 部署 氏名 GP 部署 氏名 2 2018/12/1(土) D 部2 氏5 C 部7 氏5# G 部2 氏6 E 部1 氏9# 3 2018/12/2(日) D 部2 氏7 G 部6 氏5 E 部2 氏9 B 部2 氏1 4 2018/12/3(月) G 部1 氏8 A 部6 氏9 F 部8 氏1# 5 2018/12/4(火) F 部4 氏7# A 部9 氏4# D 部4 氏8 C 部1 氏1
＞何に紐付けをすればいいのか思い浮かばず、、、
Sheet2の"#"の数と同じだけのグループ数に(左から）色を付ければいいので・・
B2セルを先頭にした範囲に以下の条件で、条件付き書式で着色
条件式 ↓ =FLOOR(COLUMN(C2)/3,1)<=SUM(N(RIGHT(Sheet2!$B2:$M2)="#"))
（半平太） 2018/12/22(土) 00:05
すみません、再現ができないです。
DGJの部署の列に、日付のシリアル値が文字列で表示されます。
また、氏名が空欄になります。
四人目LMN列のGP、部署、氏名は全て空欄になります。
あと、サンプルデータはA列に日付と曜日が収まってますが、結果図はA列、B列に日付曜日がわかれてます。以前のデータもよく見ると同じ形ですが、前回は気づかずにサンプルと結果図は同じ形にしてました。
またしっかりと時間をとって確認します。明日以降になると思います。
（冬将軍） 2018/12/22(土) 01:25
レイアウトが違ったらワークしないです。
Sheet1(結果図）はそちらの希望するレイアウトですよね？(質問当初はそうだった）
Sheet2の方は、曜日欄は不要なので(あっても無駄）設けていないです。 ※曜日が何かは、セル書式で日付と一緒に表示させられるので。。
上の認識で合っていませんか？
（半平太） 2018/12/22(土) 08:16
<Sheet1 結果図> 行 ____A____ __B__ _C_ __D__ __E__ _F_ __G__ __H__ _I_ __J__ __K__ _L_ __M__ __N__ 1 日付 曜日 GP 部署 氏名 GP 部署 氏名 GP 部署 氏名 GP 部署 氏名 2 2018/12/1 E 部1 氏9 D 部2 氏5 G 部2 氏6 C 部7 氏5
<Sheet2 サンプルデータ> 行 ______A______ _B_ __C__ __D__ _E_ __F__ __G__ _H_ __I__ __J__ _K_ __L__ __M__ 1 日付 GP 部署 氏名 GP 部署 氏名 GP 部署 氏名 GP 部署 氏名 2 2018/12/1(土) D 部2 氏5 C 部7 氏5￥ G 部2 氏6 E 部1 氏9# （冬将軍） 2019/02/18(月) 10:03
C2セル =IFERROR(SUBSTITUTE(SUBSTITUTE(INDEX(Sheet2!2:2,MOD(AGGREGATE(15,6,20000-((RIGHT(Sheet2!$D2:$M2)="#")-(RIGHT(Sheet2!$D2:$M2)="\"))*10000+MATCH(Sheet2!$B2:$M2,組名,0)*100+{2,0,0,5,0,0,8,0,0,11,0,0},FLOOR(COLUMN(C1)/3,1)),100)+MOD(COLUMN(A2)-1,3)),"#",""),"\",""),"")
（半平太） 2019/02/18(月) 14:41
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.