エクセル関数でお金の支払い方法を教えてください
- エクセル関数を使ってお金の支払い方法を教えてください。セルの値や支払い日を設定し、未納分を表示する方法を知りたいです。
- エクセルの支払い管理方法について教えてください。支払いの順番や未納分の表示、中抜け状態の設定方法について詳しく知りたいです。
- セルの値と支払い日を設定し、エクセル関数を使って支払い管理を行いたいです。未納分を表示し、中抜け状態を設定する方法を教えてください。
- ベストアンサー
エクセル関数を至急教えて下さい
お金の支払い方法で話しを進めて行きます。 セルＫ6が7月分 Ｌ6が6月分 Ｍ6が5月分・・・・・・・・Ｒ6が12月分と設定とします 本来の支払いは、Ｒ6・Ｑ6・Ｐ6・・・Ｋ6と古い順に支払って行かなければなりません。 しかし、Ｎ6の4月分、Ｐ6の2月分、Ｒ6の12月分と抜けて支払いをしています。 Ｋ6 Ｌ6 Ｍ6 Ｎ6 Ｏ6 Ｐ6 Ｑ6 Ｒ6 ■ ■ ■ 7/16 ■ 6/30 ■ 5/20 支払いした日付けを「a/b」とし表示し、未納分を■としています。 上記状態で、セルＯ6、Ｑ6が未納分として残っているのが分かります。 この状態を「中抜け」設定としたいのです。 その後、Ｏ6，Ｑ6が7/17に支払いをしたとすると Ｋ6 Ｌ6 Ｍ6 Ｎ6 Ｏ6 Ｐ6 Ｑ6 Ｒ6 ■ ■ ■ 7/16 7/17 6/30 7/17 5/20 となるため、「中抜け」状態は解消となります。（Ｓ6には中抜け表示をさせない） 以上の様な内容でお分かりになるでしょうか？
- morimoripapa
- お礼率33% (2/6)
- オフィス系ソフト
- 回答数6
- ありがとう数2
- みんなの回答 （6）
- 専門家の回答
質問者が選んだベストアンサー
No.3～5です。 ＞Ｋ６が空白、または他の文字が入ると「中抜け」と判断されます。 回答した数式は、日付が入力されていないセルには全て「■」が入力されていることを前提に、組み立てられています。 つまりK6セルを挿入し、そこが空白とか他の文字が入っている状態というのは、K6に日付が入力されているのと同じに扱われることになります。したがって、K6より右側に「■」が存在すれば、当然「中抜け」と判断されます。K6にも「■」を入力すれば、セル挿入前と同様に数式が機能すると思います。 で、もしも追加のご質問が、機能しない数式を機能させるとかではなくて、とにかく「－」を「■」と同じ働きをする文字として指定したいというのが希望であれば、次式のようにすれば、「－」と「■」の区別がなくなります。足し算をするとOR条件になるのです。 =if(countif(k6:r6,"■")+countif(k6:r6,"－")>min(index(column(k6:r6)+((k6:r6="■")+(k6:r6="－"))*999,))-column(k6),"中抜け","")
その他の回答 (5)
- MarcoRossiItaly
- ベストアンサー率40% (454/1128)
No.3・4です。度々すみません…。次式のほうがNo.4のものより更に短く書けたのでした。もちろん計算の結果は、変わりません。 =if(countif(k6:r6,"■")>min(index(column(k6:r6)+(k6:r6="■")*999,))-column(k6),"中抜け","") 変更した箇所は、SMALL関数の第2引数の「,1」という部分を削除するとともに、SMALL関数をMIN関数に置き換えました。これでも結果は同じです。これでもう修正ないと思います、すみませんm(_ _)m ところで、No.1さんへの補足にお書きになった数式ですが、「条件付きで」だいたい大丈夫？そうです（それでも「■」の並びによってはMATCH関数の動きが怪しいケースもあるかもしれませんが…。）。つまり、少なくとも、最も左に記入される日付が常に、最も新しい日付である必要があります。新旧どんな順番で並ぶか分からない場合は、正しく機能しません。例えば、質問文のとおりに日付が記入されている状態から「7/16」と「5/20」の位置を交換してみると、「中抜け」が表示されません。
- MarcoRossiItaly
- ベストアンサー率40% (454/1128)
No.3です、何度もすみません。次式のほうがNo.3のものより若干、短く書けます。計算の結果は、変わりません。 =if(countif(k6:r6,"■")>small(index(column(k6:r6)+(k6:r6="■")*999,),1)-column(k6),"中抜け","") 変更した箇所は、INDEX関数の中身とSMALL関数の第2引数です。今回のINDEX関数は、日付であるセルをその列番号に、「■」であるセルをその列番号と「999」の合計に置き換えることでできる配列を生成。なお999か月は、83年に相当。
- MarcoRossiItaly
- ベストアンサー率40% (454/1128)
=if(countif(k6:r6,"■")>small(index((k6:r6<>"■")*column(k6:r6),),countif(k6:r6,"■")+1)-column(k6),"中抜け","") 解説。 「countif(k6:r6,"■")」は、K6:R6の範囲にある「■」の個数を計算。INDEX関数は、「■」であるセルを「0」、日付であるセルをその列番号に置き換えることでできる配列を生成。「countif(k6:r6,"■")+1」は、K6:R6の範囲にある「■」の個数に「1」を足した数を計算。「small-column」は、列番号どうしの引き算により（SMALL関数に列番号を計算させている）、日付がある列のうち最も左の列より左にある「■」の個数を計算。 要するに、質問文のとおり日付が入力されているならば、全体の「■」の個数は5個、最も左にある日付「7/16」より左にある「■」の個数は3個であり、「5>3」が成り立つので、「中抜け」と判定される数式になっています。
- KURUMITO
- ベストアンサー率42% (1835/4283)
S６セルには次の式を入力してはどうでしょう。 =IF(COUNT(K6:R6)=0,"",IF(COUNTIF(INDEX(K6:R6,MATCH(1,INDEX(SIGN(K6:R6),0),0)):R6,"")>0,"中抜け",""))
お礼
ご指導ありがとうございます。 今後ともよろしくお願いいたします。
補足
ありがとうございます。 セルの場所によって「中抜け」とならない所があります・・・・。
- CC_T
- ベストアンサー率47% (1038/2201)
> この状態を「中抜け」設定としたいのです。 支払日の間が空いていたら、S6に「中抜け」って文字を表示したいのでしょうか？ 例えば、COUNTIFでK6からR6までの空白セルの数を数えて、範囲内の左端からその数分のセルを合計した結果が０以上ならば中抜け状態と判断できそうですね。合計取ったセル範囲のどこかに日付が入っているってことですから。 SUMの範囲を左右に可変するにはOFFSETを使って、 S6 =IF(SUM(OFFSET(K6,0,0,1,COUNTIF(K6:R6,"")))>0,"中抜き",""） ですかね。
お礼
ご指導ありがとうございます。 今後ともよろしくお願いいたします。
補足
早々ありがとうござます。 コピーして貼り付けして見ましたが・・・・ 値が入力出来ませんでした(>_<) ちなみにこう言うのも有りましたが、いかがでしょうか？ S1=IF(COUNTIF(K6:R6,"■")=0,"",IF(MATCH("■",K6:R6,1)>MATCH(MAX(K6:R6),K6:R6,0),"中抜け",""))
関連するQ&A
- ある値以上のデータの個数を表示させる関数は？
25行目から下に、複数のデータがあります。 E,G,I,K,M,O,Q,S,U,W の列だけで、 値が9以上であるセルの個数を 「 B列 」 に表示させる関数はどのようになりますでしょうか？ よろしくお願い致します。 下記例の場合は、 9以上の値は、 4つありますのでB列に 「 4 」 と表示させます。 --------------- 実行前 A B C D E F G H I J K L M N O P Q R S T U V W X 25 10 8 7 9 12 3 25 1 4 5 ・ ・ 実行後 A B C D E F G H I J K L M N O P Q R S T U V W X 25 4 10 8 7 9 12 3 25 1 4 5 ・
- ベストアンサー
- オフィス系ソフト
- エクセルについて
エクセルにおいて、下記のように１～５のみ残し、その他無くなった数字は見えなくする、もしくわ文字の色を白にして見えないようにしたいと思っています。 A B C D E F G H I J K L M N O P Q R S T U １ 0 0 0 0 0 1 2 3 3 3 3 3 4 5 5 5 5 5 5 5 5 ２ 0 0 1 2 2 2 2 3 4 4 5 5 5 5 5 5 5 5 5 5 5 ↓ ↓ A B C D E F G H I J K L M N O P Q R S T U １ 1 2 3 3 3 3 3 4 5 ２ 1 2 2 2 2 3 4 4 5 私は基本的な関数は使用出来ますが、VBAは勉強し始めたばかりです…。 良い方法をご教示頂ければと思っております。 よろしくお願いいたします。 ※記入した例において、１～５の列がズレていますが、行も列も同じばしょのままでお願いします。
- 締切済み
- オフィス系ソフト
- エクセル・時間の合計が合わない
実労働時間の１ヶ月の合計をだすのに、ＳＵＭを使ったのですが、 合計がすごく小さい数字になります。 表示は※※：※※で表示しています。 表の内容は下記の通りです。 ４行目に題名 ・Ｂ出勤時刻・Ｃ休憩開始・Ｄ休憩終了・Ｅ退社時刻 Ｆ４・Ｉ４・Ｌ４・Ｎ４・Ｑ４＝０：００ Ｇ４・Ｊ４・Ｍ４・Ｏ４・Ｒ４＝１７：００ Ｆ =IF($E5-F$4>0,$E5-F$4,0)-IF($B5-F$4>0,$B5-F$4,0) Ｇ =IF($E5-G$4>0,$E5-G$4,0)-IF($B5-G$4>0,$B5-G$4,0) Ｉ =F5-SUM(J5:$K5) Ｌ =IF($D5-L$4>0,$D5-L$4,0)-IF($C5-L$4>0,$C5-L$4,0) Ｍ =IF($D5-M$4>0,$D5-M$4,0)-IF($C5-M$4>0,$C5-M$4,0) Ｎ =L5-SUM(O5:$P5) Ｏ =M5-SUM(P5:$P5) Ｑ =I5-N5 （実労働時間１７時までの１日の計） Ｒ =J5-O5 （実労働時間１７時からの１日の計） Ｑに=SUM(Q5:Q14) Ｒに=SUM(R5:R14) を入力しましたが、まったく違う時間数になります。 どうすれば正しい計算式がでるのでしょうか？ お教え願いたくお願い申し上げます。
- ベストアンサー
- オフィス系ソフト
- 順列・数え上げ
よろしくお願いします。 ここに下のような390個の文字があります。 (A,B,C,D,E,F,G,H,I,J,K,L,M がそれぞれ10個ずつ、 N,O,P,Q,R,S,T,U,V,W,X,Y,Z がそれぞれ20個ずつあります。) この390個の文字から235文字を選んで一列に並べる方法は全部で何通りありますか。 A B C D E F G H I J K L M A B C D E F G H I J K L M A B C D E F G H I J K L M A B C D E F G H I J K L M A B C D E F G H I J K L M A B C D E F G H I J K L M A B C D E F G H I J K L M A B C D E F G H I J K L M A B C D E F G H I J K L M 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 N O P Q R S T U V W X Y Z N O P Q R S T U V W X Y Z N O P Q R S T U V W X Y Z N O P Q R S T U V W X Y Z N O P Q R S T U V W X Y Z N O P Q R S T U V W X Y Z N O P Q R S T U V W X Y Z N O P Q R S T U V W X Y Z N O P Q R S T U V W X Y Z N O P Q R S T U V W X Y Z N O P Q R S T U V W X Y Z N O P Q R S T U V W X Y Z N O P Q R S T U V W X Y Z N O P Q R S T U V W X Y Z N O P Q R S T U V W X Y Z N O P Q R S T U V W X Y Z N O P Q R S T U V W X Y Z N O P Q R S T U V W X Y Z N O P Q R S T U V W X Y Z 以下、私が考えたことを書きます。 この390個の文字から235個の文字を選ぶ組み合わせの総数は、 (Σ[k=0～10]x^k)^13*(Σ[k=0～20]x^k)^13 を展開したときのx^235の係数ですから、 23463540513956137996043929988 通りだということは分かります。 この23463540513956137996043929988 通りのそれぞれについて235個の文字 の順列(同種のものを含む順列)を数え上げれば答えは出ると思いますが、これは あまりにも大変な作業です。 何かよい知恵はないでしょうか。
- ベストアンサー
- 数学・算数
- Excelで抽出？カウント
VBAマクロや関数でデータの抽出とカウントをしたいんですが難しくて混乱したのでヘルプお願いします。 K29ア→K20に２ L29イ→L20に６ M29ア→M20に５ N29イ→N20に３ O29ア→O20に５ P29ア→P20に６ Q29イ→Q20に７ R29イ→R20に８ S29ア→S20に９ T29イ→T20に４ U29イ→U20に３ とありまして"ア"に関連するセルの数値を抽出して何の数値が何個あるか観れるようにしたいです。"イ"についてもそのようにしたいです。 お願いします。
- 締切済み
- Excel（エクセル）
- Excelで質問ですが何処が間違ってますか？
Excel2007のマクロで置き換えと言うか代入という表現が正しいのかは不明なんですが、次のことがしたいです。 １、セルK29からZ29までかそれ以上のセルの1つずつにデータが4つずつ入ってます。 ２、それらのデータを、入ってる形によって別な記号に変えてK30からZ30までかそれ以上に入れたいです。 K29アイウエ→K30にA L29アイエウ→L30にB M29アウイエ→M30にC N29アイウエ→N30にA O29アウエイ→O30にD P29アエイウ→P30にE Q29イアウエ→Q30にF R29イアエウ→R30にG S29イウアエ→S30にH T29イウエア→T30にI U29イエアウ→U30にJ V29イエウア→V30にK W29ウアイエ→W30にL X29ウアエイ→X30にM Y29ウイアエ→Y30にN Z29ウイエア→Z30にO ……… と、この様になんですが。 で、 =IF(K29="","",MID("ABCDEFGHIJKLMNO",FIND(","&K29&",",",アイウエ,アイエウ,アウイエ,アウエイ,アエイウ,イアウエ,イアエウ,イウアエ,イウエア,イエアウ,イエウア,ウアイエ,ウアエイ,ウイアエ,ウイエア,")/4+0.75,1)) と関数でこの様に書きましたがなぜかズレていました。 何処が間違ってるか教えてください。
- ベストアンサー
- Excel（エクセル）
- EXCELの関数で...
以下のようなことが関数でできないでしょうか。 L1～L10に文字 M1～M10に文字 N1～N10に日付け が、入ってます。 K1～K10は空白ですがここに1～10の数字をランダムに入れます。 そのとき、Kに1の入った行（L,M,N,)の内容をA5,B5,C5に、 Kに2の入った入った行（L,M,N,)の内容をA6.B6,C6にと Kに10が入ったところまでを順次行いたいのですが できるでしょうか？ どなたか、よろしくお願いいたします。
- ベストアンサー
- オフィス系ソフト
- エクセル 文字列を分割するマクロについて
１行目から200行目くらいまで、Ａ列に次のようなデータが入っています。 （Ａ1セル） A 1 Ａ,Ｂ,Ｃ,Ｄ,Ｅ,Ｆ,Ｇ,Ｈ,Ｉ,Ｊ,Ｋ,Ｌ,Ｍ,Ｎ,Ｏ,Ｐ,Ｑ,Ｒ,Ｓ このデータを下記のようにカンマごとに別のセルに分割するマクロを 教えていただけないでしょうか。 Ａ1に「Ａ」を、Ｓ1に「Ｓ」を入れたいと思っています。 Ａ Ｂ Ｃ ・・・ Ｒ Ｓ 1 Ａ Ｂ Ｃ ・・・ Ｒ Ｓ
- ベストアンサー
- オフィス系ソフト
補足
もう少し教えて頂いても良いですか？ 月日順番で行くとＫ６の部分に今後セルを挿入して行き、増える予定なのですが、現段階では Ｋ６が空白、または他の文字が入ると「中抜け」と判断されます。 なので、「ハイフン（－）」を入れると中抜け判定されない方法は有りますか？ 何度も回答頂き申し訳御座いませんが、今一度お力を貸して頂ければと思います。