- ベストアンサー
配車実績の集計で悩んでいる方へ
MackyNo1の回答
- MackyNo1
- ベストアンサー率53% (1521/2850)
数式を分解して説明すると、 (OFFSET(F6,SMALL(IF((OFFSET(B6,0,0,10,1)=B6)*(MATCH(OFFSET(F6,0,0,10,1)&"",OFFSET(F6,0,0,10,1)&"",0)=ROW($A$1:$A$10)),ROW($A$1:$A$10),50000)-1,1),0)=$Y$6:$Y$60) の部分は (OFFSET(B6,0,0,10,1)=B6) B6セル以下10個分のセルの値がB6と等しく、 (MATCH(OFFSET(F6,0,0,10,1)&"",OFFSET(F6,0,0,10,1)&"",0)=ROW($A$1:$A$10) かつF6セルから10個分のセルの値（配列）を、同じ範囲で検索した時、１から10（ROW関数の返り値）に等しい（初めて出てくる値と同じ）なら １から10（ROW関数の返り値）にそれ以外の場合は50000という大きい数字の配列を返す数式になっています。 その外側の SMALL(IF((・・・・)),1) の部分は、上記で得られた配列で最も小さい数字（すなわちF6セル以下で初めてF6セル以下の10個のセルが初めて出現する位置）が求められます。 OFFSET(F6,SMALL(IF((・・・・))-1,1,0) F6セルから計算して上記の数値マイナス１した場所（Y列のデータに相当）のセルの値を求めています。 （上記の数式)=$Y$6:$Y$60) 1つ目のデータがY列のデータと等しい配列を求めています。 最後にSUMPRODUCT関数で、この条件を3つおよびAB列のデータの４つの配列を掛け算して、YからABセルですべての組み合わせが一致するデータの距離を探しています。
関連するQ&A
- エクセルでの集計で悩んでます。
エクセルでの集計で悩んでます。 （サンプル） ●番号・出荷先・入荷先がＢ列・Ｆ列・Ｈ列に次のように並んでいます。 Ｂ列 Ｆ列 Ｈ列 ( 4行目) 番号 出荷先 入荷先 ( 5行目) （空欄） （空欄） （空欄） ( 6行目) １ Ａ あ ( 7行目) １ Ａ あ ( 8行目) １ Ｂ あ ( 9行目) ２ Ａ い (10行目) ２ Ａ あ (11行目) ３ Ｂ う (12行目) ４ Ｂ お (13行目) ４ Ｃ お (14行目) ５ Ａ あ (15行目) ５ Ａ あ ※全部で5,000行くらいあります。 （直送マスタ） ●Ｙ列～ＡＡ列に「直送マスタ」があります。 Ｙ列 Ｚ列 ＡＡ列 (4行目) 直送マスタ (5行目) 出荷先 入荷先 距離 (6行目) Ａ あ 10 (7行目) Ａ い 20 (8行目) Ａ う 30 (9行目) Ｂ あ 40 (10行目) Ｂ い 50 (11行目) Ｂ う 60 (12行目) Ｃ あ 70 (13行目) Ｃ い 80 (14行目) Ｅ う 90 ※200行くらいあります。 （混載マスタ） ●ＡＢ列～ＡＨ列に「混載距離マスタ」を作成しています。 ＡＢ列 ＡＣ列 ＡＤ列 ＡＥ列 ＡＦ列 ＡＧ列 ＡＨ列 (4行目) 混載マスタ (5行目) 出荷先１ 出荷先２ 出荷先３ 入荷先１ 入荷先２ 入荷先３ 距離 (6行目) Ａ Ｂ あ 100 (7行目) Ａ Ｃ あ い う 110 (8行目) Ａ あ い 120 (9行目) Ｂ Ｃ お 130 ※100行くらいあります。 ※１つの番号に対して「発場所」もしくは「着場所」が２つ以上あるものが混載です。 ※番号は車輛の番号であり、(サンプル)の「3」番「5」番のように１箇所の出荷先に 対して入荷先も１箇所の場合は「直送」で「1」番「2」番「4」番は混載となります。 (1)こういった情報をもとに、Ｗ列に混載の合計距離を出したいと思っています。 （サンプル）の例で考えると、 セル「Ｗ６」に混載マスタを参照して「100」と表示 セル「Ｗ９」に混載マスタを参照して「120」と表示 セル「Ｗ１２」に混載マスタを参照して「130」と表示 といったようにＢ列の番号が変わる度にＷ列に混載の距離が出力されるような関数を設定したいのですが、 教えていただけないでしょうか？ (2)直送マスタは別の関数を設定して何とか表示できているのですが、 セル「Ｗ１１」に直送マスタを参照して「60」と表示 セル「Ｗ１４」に直送マスタを参照して「10」と表示 といったように、直送の距離も混載の距離と合わせて表示できるようなマクロを教えていただけないでしょうか。
- ベストアンサー
- オフィス系ソフト
- 重複入力の回避のＶＢＡをご教示ください
６行目のセル（６行、Ｃ列）に顧客番号を入力し、Ｆ列以降に、セル（６行、Ｃ列）の顧客番号の内容を入力しています。 その入力時に、顧客番号の重複入力を避けるためのチェック及び対処処理をご教示お願い致します。 (1)・セル（６行、Ｃ列）に顧客番号を入力し、Ｆ列からＨ列に、セル（６行、Ｃ列）の顧客番号の内容を入力する。 (2)・セル（７行、Ｃ列）に顧客番号を入力し、Ｆ列からＨ列に、セル（７行、Ｃ列）の顧客番号の内容を入力する。 （イ）・セル（７行、Ｃ列）に顧客番号を入力し、エンターキーを押した時点でセル（６行、Ｃ列）の顧客番号と重複していなかチェックする ●重複していない場合は、カーソルをセル（７行、Ｆ列）へ移動させる ●重複している場合は、「同じ番号があります」とメッセージボックスを表示する。 メッセージボックスのキャンセルボタンをクリックするとセル（７行、Ｃ列）の重複番号が削除されカーソルはセル（７行、Ｃ列）へ (3)・セル（８行、Ｃ列）に顧客番号を入力し、Ｆ列からＨ列に、セル（８行、Ｃ列）の顧客番号の内容を入力する。 （イ）・セル（８行、Ｃ列）に顧客番号を入力し、エンターキーを押した時点でセル（６行、Ｃ列）とセル（７行、Ｃ列）の顧客番号と重複していなかチェックする ●重複していない場合は、カーソルをセル（８行、Ｆ列）へ移動させる ●重複している場合は、「同じ番号があります」とメッセージボックスを表示する。 メッセージボックスのキャンセルボタンをクリックするとセル（８行、Ｃ列）の重複番号が削除されカーソルはセル（８行、Ｃ列）へ (4)・セル（９行、Ｃ列）に顧客番号を入力し、Ｆ列からＨ列に、セル（９行、Ｃ列）の顧客番号の内容を入力する。 （イ）・セル（９行、Ｃ列）に顧客番号を入力し、エンターキーを押した時点で、セル（６行、Ｃ列）とセル（７行、Ｃ列）とセル（８行、Ｃ列）の顧客番号と重複していなかチェックする ●重複していない場合は、カーソルをセル（９行、Ｆ列）へ移動させる ●重複している場合は、「同じ番号があります」とメッセージボックスを表示する。 メッセージボックスのキャンセルボタンをクリックするとセル（９行、Ｃ列）の重複番号が削除されカーソルはセル（９行、Ｃ列）へ (5)・(1)～(4)を１セットとお考え下さい ●１０行から１３行の４行を１セットとして、セル位置は変わりますが、(1)～(4)の処理をしたい。 ●最終 ７０２行から７０５行まで、(1)～(4)の処理をしたい。 宜しくお願い致します。
- ベストアンサー
- その他(ソフトウェア)
- エクセルのマクロ記述について
下記の処理をエクセルのマクロで行いたいのですが、どのように記述したよいか教えてください。 4行目から入力されている行まで下記の処理をマクロで行う。 １．J列のセル入力がCIRCLEの行で、B～E列の数値が同じセル間を結合する。 ２．K列のセル入力がCIRCLEの行で、F～I列の数値が同じセル間を結合する。 ３．J列のセル入力がOBLONG_XまたはOBLONG_Yの行で、B,D列及びC,E列の数値が同じ場合、B列をB列の数値ＸC列の数値とし、B～E列のセルを結合する。 ４．K列のセル入力がOBLONG_XまたはOBLONG_Yの行で、F,G列及びH,I列の数値が同じ場合、F列をF列の数値ＸG列の数値とし、F～I列のセルを結合する。 ５．J列のセル入力がOBLONG_XまたはOBLONG_Yの行で、B,D列及びC,E列の数値が同じでない場合、B列をB列の数値ＸC列の数値とし、B,C列のセルを結合する。又、D列をD列の数値ＸE列の数値とし、D,E列のセルを結合する。 ６．K列のセル入力がOBLONG_XまたはOBLONG_Yの行で、F,G列及びH,I列の数値が同じでない場合、F列をF列の数値ＸG列の数値とし、F,G列のセルを結合する。又、H列をH列の数値ＸI列の数値とし、H,I列のセルを結合する。 ７．J列のセル入力がSHAPEの行で、B～E列は何もしない ８．K列のセル入力がSHAPEの行で、F～I列は何もしない
- 締切済み
- Visual Basic
- EXCEL2007で、セルに「ユ－ザー定義(yy/m/dd)」で日付を入力し保存しましたが、日付がいつの間にか変更されています。
EXCEL2007を使用しています。 会社の出荷リストとして使っています。 セルの項目は、１列目に下記項目を入れ２列目から実際のデータを入れています。（データは６２００行程使っています。） Ａ２（セル番号）・・・製造番号 Ｂ２（セル番号）・・・台数 Ｃ２（セル番号）・・・支営番号 Ｄ２（セル番号）・・・出荷日 Ｅ２（セル番号）・・・代理店名 Ｆ２～Ｑ２・・・いろいろ セルのＤ列に、セルの書式設定で「ユ－ザー定義(yy/m/dd)」で日付を入力し、保存しましたが、いつの間にか日付が変更されている箇所があります。 最初は、出荷日や製造番号順に並び替えをするからこのような現象が起こるのかと思いましたが、並び替えをやめても、いつの間にか日付が変更されている箇所があります。 ご存知の方、よろしくお願いいたします。
- 締切済み
- オフィス系ソフト
- Excel2003の集計および印刷について
いつもお世話になっております。 新年早々ですが、今回はExcelについて質問させてもらいます。 現在、とあるデータの集計を行い、印刷を行う方法を考えていますが、あまり詳しくないので困っています。 何かいい方法があればと思い投稿しました。 まずExcelファイルに「入力用シート」と「出力用シート」の二つのシートがあります。 「入力用シート」には、 C1セル、D1セル、E1セル、F1セル C2セル、E2セル、F2セル に表題や番号などを入力します。 また、下記のようにA1セルとB1セルから下へずっとデータを入力します。データ量は多いときは１０００行ぐらいです。 例） 1000 5 1200 2 900 10 800 2 1000 1 2400 3 あとフォームのボタンを作りこれを押すと、「出力用シート」にC1,D1,…F2までのセルをそのままコピーし、A1、B1から下へ入力されているデータのみを集計し表示を行いたいと思ってます。 集計の条件はA列ごとに集計し降順で表示です。 結果として「出力用シート」には 以下のように表示したいと思ってます。 あいうえお かきくけこ 111111 222222 さしすせそ 333333 444444 2400 3 1200 2 1000 6 900 10 800 2 ですが、A列とB列に入力するデータが多くなった際、印刷すると右半分が白紙の状態で、できれば右半分にもと思ってます。つまり１枚に２ページ分印刷と言うことです。 データは１枚に３０行ぐらいで左半分と右半分で６０件ぐらい表示。 （文字を大きく表示させるため） それ以上になると２枚目に１枚目と同じように１行目２行目は１枚目と同じで印刷できればと。 例） -----１枚目----- あいうえお かきくけこ 111111 222222 さしすせそ 333333 444444 2400 3 440 2 1200 2 430 22 1000 6 415 9 900 10 409 2 800 2 399 8 500 1 395 5 450 3 393 2 -----２枚目----- あいうえお かきくけこ 111111 222222 さしすせそ 333333 444444 391 1 320 2 311 22 説明が長くなりましたが、何卒よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- 再び質問。エクセルで集計。
昨日、「エクセルまたは他のフリーソフトで集計したいです。」 と質問をして、ベストアンサーさんから教えて頂いた下記の方法で、 自宅パソコンのWindows 7のエクセルでは出来ました。 そのデーターを会社のWindows XPのエクセル2000で開くと、 シート２の名前が表記されるところに「＃NAME?」となってしまいました。 私が最初質問した時にWindows 7と書いたので、それに沿って教えて 頂いたのだと思います。 最近のエクセルでしかできない事なのでしょうか？ それともどこかを変更すると同じ事ができるのでしょうか？ エクセル初心者でわからない事だらけです。 教えて下さい！よろしくお願い致します。 ベストアンサーの方の回答↓ ----------------------------------------------------------- シート１は元の表でお求めの表をシート２に作るとします。 シート１ではB１セルからH１セルにかけて月から日まで入力します。 氏名はA２セルから下方にあるとします。(1),(2),(3)の選択種がBからH列の２行目以降にに入力されるとします。 J列からP列を作業列として、J２セルには次の式を入力してP２セルまでドラッグコピーしたのちに下方にもドラッグコピーします。 =IF(OR($A2="",B$1="",B2=""),"",B$1&B2&(COUNTIF(B$1:B1,B2)+1)) シート２に移ってA１セルから３行おきに月（A1セル)、火（A４セル）、水（A７セル）・・とA１９セルまで入力します。 B１セルからB３セルまでに(1),(2),(3)を入力してそれを下方に繰り返し表示させます。 C１セルには次の式を入力して横方向にドラッグコピーしたのちに下方にもドラッグコピーします。 =IFERROR(INDEX(Sheet1!$A:$A,MATCH(INDEX($A:$A,ROUNDUP(ROW(A1)/3,0)*3-2)&$B1&COLUMN(A1),INDEX(Sheet1!$J:$P,1,ROUNDUP(ROW(A1)/3,0)):INDEX(Sheet1!$J:$P,10000,ROUNDUP(ROW(A1)/3,0)),0)),"")
- ベストアンサー
- Windows XP
- Excelで集計に関する関数の質問です
添付した図を基に質問します。４行目をウィンドウ枠固定して表示してあります。 行列番号を見てもらえたら分かると思いますが、要所でセルを結合してあります。 セルB6に=IF(B5="","",(IF(B5>1999,0,IF(E5<501,0,IF(AND(B5>499,E5<2001),(TIME(INT(E5/100),MOD(E5,100),0)-TIME(INT(B5/100),MOD(B5,100),0)),IF(B5<500,TIME(INT(E5/100),MOD(E5,100),0)-(TIME(INT(500/100),MOD(500,100),0)),IF(E5>2000,TIME(INT(2000/100),MOD(2000,100),0)-TIME(INT(B5/100),MOD(B5,100),0),0))))))*24) D6に=IF(B5="","",IF(AND(B5<500,E5>500),(TIME(INT(500/100),MOD(500,100),0)-TIME(INT(B5/100),MOD(B5,100),0)),IF(AND(B5<2000,E5>2000),(TIME(INT(E5/100),MOD(E5,100),0)-TIME(INT(2000/100),MOD(2000,100),0)),IF(AND(B5>499,E5<2001),0,(TIME(INT(E5/100),MOD(E5,100),0)-TIME(INT(B5/100),MOD(B5,100),0)))))*24) これは、項目A列５行目を参照しています。以下オートフィルします。 例えば７時から１０時１０分の差を求める場合、B6セルに700、D6セルに1010と１分単位で入力し、表示形式は0.0" H"です。 項目C,Dも同様で、項目C内E列には１時間３０分の場合１.5と入力し30分単位です。表示形式は同様。 A列の日付欄には、１月１日の場合１／１と記入します。表示形式は日付の月/日です。 上記内容で添付図の上の表(A3:S30)項目B～D列のデータを下の表（A32:S39）で集計しようと思います。 1、F35～F38に月ごとの時間の計を表示させる関数 2、J35～J38には、月ごとの時間*単価を項目別に計算した値の計を表示させる関数 3、P35～P38にも上記同様の計を表示させる関数 以上、1,2,3に入力する関数が知りたいので、宜しくお願いします。 なお不明な点がありましたら随時補足していこうと思います。
- ベストアンサー
- オフィス系ソフト
- エクセルファイルの結合はどうするの？
＊ＡとＢのエクセルファイルは同書式です (1)ＡとＢのエクセルファイルを結合してＡ・Ｂの内容を含んだＣのファイルをつくれませんか？（説明不足ですかね）／列だけではなく行も同時に結合？挿入したＣのファイルをつくれませんか？ (2)ＡとＢのエクセルファイルそれぞれのＨ列の１５００行に数字が入力されてます／ＡＢファイルのＨ列の１５００行のセルには必ずどちらか一方にしか数字は入力されてません／ＡＢを結合？させたＨ列の新たなＣファイルを作りたい
- ベストアンサー
- Windows XP
- 平均点以上の児童を抽出したい
シート１のB１セルは「氏名」と入力し以下のセルには学年児童の氏名が入力されています。 B1セルの隣、C１セルには「国語２４年」と入力し、以下のセルには児童の国語２４年の得点を入力。同様にC1セルの隣のD1セルには「算数２４年」、その隣は「国語２３年」・・・・とあり最後はH１セルに算数２２年となって各列に得点が入力されています。 同じくシート１の K１列からQ１列にB1～H１に入力している項目をコピーしています。そして L1～Q１の直下のセル（L2～Q2）に各年度の平均点以上の数式（例 ＞８０など）を入力しています。 やりたいことは、B１～H１の下に入力されている児童の得点からL2～Q2の数式に該当する児童と得点を表示させたいのです。 以前に同様のことを行ったのですが、やり方をすっかりと忘れてしまい困っています。 どなたか、ご存じの方、ご教示ください。
- ベストアンサー
- Excel（エクセル）
- エクセルの配列関数の制限について
エクセルの配列関数の制限について Ｖ列にＸ列・Ｙ列・Ｚ列・ＡＡ列・ＡＢ列・ＡＣ列・ＡＥ列・ＡＧ列・Ｐ列を参照した配列関数を 下のように入力しています。 {=SUM((Y$1:Y$500=F7)*(Z$1:Z$500=H7)*(AA$1:AA$500)*(P7="○"),(AB$1:AB$500=F7) *(AC$1:AC$500=H7)*(AE$1:AE$500=H7)*(AG$1:AG$500)*(P7="×"))} このＹ列・Ｚ列・ＡＡ列・ＡＢ列・ＡＣ列・ＡＥ列・ＡＧ列は、ぞれぞれ200行くらいしか 文字が入力されていない場合には、配列関数の結果がうまく表示されました。 これらのセルの200行以降から300行・400行と項目を増やしていったところ、 配列関数の結果がうまく表示されないセルが出てきました。 これは、配列関数の参照するセルに制限があるということでしょうか？
- ベストアンサー
- オフィス系ソフト
補足
回答を確認しました。 何度も何度もありがとうございます。 もう少し相手していただけると助かります。 回答でいくつか質問があります。 1.「 ROW($A$1:$A$10) 」ですが、書式をコピーしても対象となるセルは変わらず、この部分の値はいつでも「1」にならないのか。 Ｂ列の最大の行数が50の場合は、10を50にしたら良いのでしょうか？ Ｂ列なので「ROW(B1:B50)」といった感じでしょうか。 2.「 OFFSET(F6,0,0,10,1)&"",0 」の「＆""」はなぜ必要になるのでしょうか。 MATCH 関数の検査値と検査範囲がいつでも同じ場合は１にならないのでしょうか？ 3.「50,000」が表示される場合はエラーなのかどうか。 お手数かけますが、この辺りを教えていただけると助かります。