[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『シート名の年月とセル内の一部の文字を含む検索』（JOJO）
新しい職場でも大変お世話になっております。
交通費を下記の様に「1・4・7・10」「2・5・8・11」「3・6・9・12」の３パターンで支給しています。 月ごとに作成しているシートの月に該当したら、交通費の欄に自動的にその金額が反映されるようにしたいのですが、いい方法が浮かびません。
A B C D ......続く
1 H21.4 山口 佐藤 鈴木
2 パターン 1・4・7・10 3・6・9・12 2・5・8・11 ←文字列で入力
3 交通費 10000
4
交通費は一か月分のデータも必要なので、各人とも下の方のセルに三か月分と一か月分の金額は入っており、H21.4ならB3セルに数字をリンクさせたいのです。 各人のデータは40行くらいです。なお、A1のシート名はワークシート名よりリンクさせています。 出来そうで出来なくて悩んでいます。よろしくお願いします。(XP,2003)
こんにちは。かみちゃん です。
> 月ごとに作成しているシートの月に該当したら、交通費の欄に自動的にその金額が反映されるようにしたい > 交通費は一か月分のデータも必要なので、各人とも下の方のセルに三か月分と一か月分の金額は入っており
この２点がわかりません。 「シートの月に該当したら」というのは、どういう操作を想定されているのですか？ 「交通費は一か月分のデータも必要なので、各人とも下の方のセルに三か月分と一か月分の金額」 という意味がわかりません。 もう少し具体的なシートレイアウトで説明していただけませんか？
(かみちゃん) 2009-03-26 21:09
かみちゃん様、説明不足で申し訳ございません。
月ごとに人件費の管理をし予算を組もうとして、月ごとにシートを作っています。 従って、H21.4～H22.3月まで１２枚のシートがあります。 >｢シートの月に該当したら」というのは、どういう操作を想定されているのですか？
シート名「H21.4」は４月なので、支給パターン「1・4・7・10」の山口が対象者となり、三か月分の交通費10000を計上します。 「H21.5」のシートになったら、今度は鈴木が対象者になるという感じです。
>｢交通費は一か月分のデータも必要なので、各人とも下の方のセルに三か月分と一か月分の金額」 という意味がわかりません。 もう少し具体的なシートレイアウトで説明していただけませんか？
社会保険料の算定資料用として、山口を例とするなら、三か月分の交通費10000（仮にB40とする）とB40÷３のセル（仮にB41とする）に数字が入っているという意味です。 (JOJO)
こんにちは。かみちゃん です。
> 三か月分の交通費10000を計上します。
どこに計上するのですか？ 「計上」とはセルに転記という意味ですか？ 具体的ら「H21.4」のシートのレイアウトを示していただけませんか？
> 社会保険料の算定資料用
シートレイアウトを示していただけませんか？
> 三か月分の交通費10000（仮にB40とする）とB40÷３のセル（仮にB41とする）に数字が入っているという
A B C
38
39
40
41
(かみちゃん) 2009-03-26 22:07
A B C
1 H21.4 個人名
2 基本給
3 手当Ａ ←支給額は入力
4 手当Ｂ
.
. 交通費 ←B40と同じ
. 続く
社会保険料控除額は計算式
38支給ﾊﾟﾀｰﾝ 1･4･7･10 ←入力 39標準報酬月額 ←関数使用 40三か月分 10000 ←入力 41一か月分 =B40/3
支給額と支給ﾊﾟﾀｰﾝ、B40以外は入力しません。 上記レイアウトでなんとか意図するところを解っていただきたいのですが。。。 (JOJO)
こんにちは。かみちゃん です。
> 上記レイアウトでなんとか意図するところを解っていただきたい
私の理解不足なのかもしれませんが、まだ解らないですので、確認させてください。
【Sheet1】
A B C D ......続く
1 H21.4 山口 佐藤 鈴木
2 パターン 1・4・7・10 3・6・9・12 2・5・8・11 ←文字列で入力
3 交通費 10000
4
【Sheet2】4月分
A B C D E
1 H21.4 山口 佐藤 鈴木
2 基本給 200000
3 手当Ａ 50000
4 手当Ｂ 25000
.
. 交通費 =B40
. 続く
社会保険料控除額は計算式
38支給ﾊﾟﾀｰﾝ 1･4･7･10 ←入力 39標準報酬月額 ←関数使用 40三か月分 10000 ←入力 ★ 41一か月分 =B40/3
上記レイアウトの場合、★の部分の取得方法ですが、 Sheet2の38行目がA1セルの月を示す値が含まれている（1･4･7･10の中に4が含まれている）ので、 Sheet2の38行目をSheet1の2行目から検索して、一致した（B2セル）場合、 その1行目の値（山口）がSheet2の1行目（山口）と一致したらその列の3行目（B3セル）の値を Sheet2の40行目（★の部分）に自動的に転記するということでしょうか？
それであれば、私でしたら、VBAで処理しますが、それでもいいのでしょうか？
(かみちゃん) 2009-03-26 23:18
書き込み遅くなりました。
【Sheet1】4月分 ←これ一枚の中で検索します。
当初書き込んだレイアウトは、2行目･3行目の項目を上に持ってきて、説明している分になります。
下記のレイアウトを参考にお願いいたします。
但し、A1のセル(H21.4)は =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))) こんな感じにしています。
別セルに書き出さないといけないと思っています。
【Sheet1】
A B C D E
1 H21.4 山口 佐藤 鈴木
2 基本給 200000
3 手当Ａ 50000
4 手当Ｂ 25000
.
. 交通費 =B40★
. 続く
社会保険料控除額は計算式
38支給ﾊﾟﾀｰﾝ 1･4･7･10 ←入力 39標準報酬月額 ←関数使用 40三か月分 10000 ←入力 41一か月分 =B40/3
Sheet1の38行目にA1セルの月を示す値が含まれている（1･4･7･10の中に4が含まれている）ので、 Sheet1の38行目をB列以降から検索して(この場合は山口)、Sheet1の★の部分に自動的に転記したいのです。 VBAは出来ないので、選択肢に入っていませんでした。(JOJO)
こんにちは。かみちゃん です。
> これ一枚の中で検索します。 > 当初書き込んだレイアウトは、2行目･3行目の項目を上に持ってきて、説明している分になります。
この意味が一瞬わからなかったのですが、 A1セルの月が40行目の支給月の中に存在したら、「交通費」の行に転記したいということでしょうか？
> VBAは出来ないので、選択肢に入っていませんでした。
当初こちらで想定していたことと、だいぶ違っているようで、やっと理解できつつありますが、 選択肢はない、ということは、提案しても受け入れることはできないということですか？ つまり、「VBAで処理するならば、このようなコードでできると思いますが」ということでコードを提示 しても、記述（貼り付け）して、実行することすらできないということでしょうか？
私は、関数での解決方法が思いつきません。 FIND関数で検索してもいいのでしょうけど、1月、2月の場合、1月、2月、10月、11月、12月のいずれかが 支給年月に指定されていると、一致してしまいそうです。
(かみちゃん) 2009-03-27 14:00
A1セルの月が38行目の支給月の中に存在したら、「交通費」★の行に転記したいということです。
｢選択肢になかった｣という意味は、自分自身で考えても理解できないので(関数なら理解可能域が広い) ただ単純に考え付かなかっただけで、貼り付けだけは出来ます。 強い意味で書いたわけではありませんので、ご理解ください。(JOJO)
こんにちは。かみちゃん です。
> 関数なら理解可能域が広い) > ただ単純に考え付かなかっただけで、貼り付けだけは出来ます。
ということならば、やっと理解できました。
当初想定していたものよりもだいぶ簡略させました。
以下のVBAを標準モジュールに貼り付けてください。
Function ISMonth(mm As Integer, rng As Range)
If Join(Filter(Split(rng.Value, "･"), mm)) <> "" Then
'指定された月が支給月の文字列に存在したら
ISMonth = True
Else
'指定された月が支給月の文字列に存在しなかったら
ISMonth = False
End If
End Function
次に、
「交通費」の行のセルに
=IF(ISMONTH(MID(A1,FIND(".",A1)+1,2),B38),B40,0)
^^^^^^^
という数式を設定してください。
これでご要望のことが実現できると思います。 なお、支給月は、「1･4･7･10」のように半角の中点で列挙するようにしてください。
ポイントは、ISMonthというユーザー定義関数を作成し、 その中で、指定された支給月セルの文字列を半角の中点で配列に区切って、その配列の中から、指定 された月を検索するようにしているところです。
(かみちゃん) 2009-03-27 14:35
UDF なら 1) Alt + F11 でVBEを起動 2) [挿入] - [標準モジュール] - 下記コードを貼り付ける 3) Alt + F11 でエクセル画面に戻る
セルに =IsMonthIn(A38,A1)
Function IsMonthIn(ByVal myPtn As String, _
ByVal myMonth As String) As Boolean
myMonth = Split(myMonth,".")(1)
With CreateObject("VBScript.RegExp")
.Pattern = "(" & Replace(myPtn, "・","|") & ")"
IsMonthIn = .test(myMonth)
End With
End Function
(seiya)
こんにちは。かみちゃん です。
> UDF なら
あまり、seiyaさんに絡むつもりはないのですが、 UDF = User Defined Function で「ユーザ定義関数」の意味なのですね。 初めて聞きました。
VBScript.RegExp は、正規表現という手法ですが、.Patternの記述の仕方が全く理解できないので、考えてもいませんでした。
質問者のJOJOさんには、わかりやすい方で解決していただければと思います。
(かみちゃん) 2009-03-27 15:03
まず、正規表現を勉強してわからないところだけの質問にはお答えしますが まるっきり知識の無い方へは説明しません。
ついでに、 Filter Function は完全一致の検索はできません。
=ISMonth(1,"10・11・12") でも True が返るはず。 (seiya)
正規表現を使用しなくてもできそう...
Function IsMonthIn(ByVal myPtn As String, _
ByVal myMonth As String) As Boolean
IsMonthIn = IsNumeric(Application.Match(Split(myMonth,".")(1), _
Split(myPtn, "・"), 0))
End Function
(seiya)
こんにちは。かみちゃん です。
seiyaさん、お時間ありましたら、教えていただきたいのですが、
seiyaさんの正規表現を使ったUDF（ユーザ定義関数）を記述した場合
=IsMonthIn("1･4･7･10","H21.4")
とした場合は、TrueではなくFalseになります。
"H21.4"をいろいろと変化させても、Trueになることはないようですが、どのように修正したらいいのかが
わかりません。
また、正規表現を使用しない場合も同様です。
> 質問者のJOJOさんには、わかりやすい方で解決していただければと思います
と書いたのですが、VBAの内容はよくわからないものの、A1セルから月を取り出さなくていいですね。 ただ、A1セルの中には、"."がないとエラーになるようなので、注意がいりますね。
> 正規表現を勉強してわからないところだけの質問にはお答えしますが > まるっきり知識の無い方へは説明しません。
勘違いしないでいただきたいのですが、まるっきりわから知識はありませんが、説明してくださいとは 言っていません。 ただ、JOJOさんは、VBAを貼り付けて実行するしかできないとおっしゃっているので、そういう方に VBScript.RegExpを使ったコードを提示するのは、どうかと思ったでけです。 また、UDFって何？とも思いました。（だから調べたわけですが）
> ついでに、 > Filter Function は完全一致の検索はできません。
そうなのですね。ご指摘ありがとうございます。 先日、初めて知りましたので、使ってみたのですが、検証は十分して動作確認したつもりですが、 seiyaさんにとっては、何かご不満な部分があったのでしょう。
やっぱり絡まないほうがよかったかな。というか・・・
(かみちゃん) 2009-03-27 15:20
私は最初の > 2 パターン 1・4・7・10 3・6・9・12 2・5・8・11 ←文字列で入力 で ・は全角と想定しています。 それと、絡む・絡まないと捕らえるのは個人の自由ですが、私はトピ主さんにコードを 提示したわけで、こんな方法もあります的なものです。
> ただ、JOJOさんは、VBAを貼り付けて実行するしかできないとおっしゃっているので、そういう方に VBScript.RegExpを使ったコードを提示するのは、どうかと思ったでけです。
なんだって同じでしょ？ > If Join(Filter(Split(rng.Value, "･"), mm)) <> "" Then (seiya)
>> ついでに、 >> Filter Function は完全一致の検索はできません。 > > そうなのですね。ご指摘ありがとうございます。 > 先日、初めて知りましたので、使ってみたのですが、検証は十分して動作確認したつもりですが、 > seiyaさんにとっては、何かご不満な部分があったのでしょう。
ここは、今一度確認しましたら、理解いたしました。
MsgBox Join(Filter(Split("1･4･7･10", "･"), "1"))
としたら、「1 10」と返ってきますので、完全一致というわけではないのですね。
勉強になりましたが、どれかひとつ一致していればいいと思っていますので、間違いではないと認識
しています。
>・は全角と想定しています。
なるほど。理解できました。seiyaさんありがとうございます。 全角／半角どちらなのでしょうね。＜JOJOさん
>> ただ、JOJOさんは、VBAを貼り付けて実行するしかできないとおっしゃっているので、そういう方に > VBScript.RegExpを使ったコードを提示するのは、どうかと思ったでけです。 > > なんだって同じでしょ？
そうですか？私個人的には、VBScript.RegExpより、よっぽど素人受けすると思いますけどね。 VBScript.RegExpが使えるに越したことはないですが、応用が利くのかと・・・ そのような難しいことしなくてもできるのになぁということです。
> 私はトピ主さんにコードを提示したわけで、こんな方法もあります的なものです。
了解しました。やっぱり絡まないほうがよかったかもしれません。
余談ですが、seiyaさんには、 私がずっと聞き取りをしていきた、スレッドに、ポンとおいしいとこ ろを持っていかれたというのが正直な気持ちなわけです。 私の未熟な言動のおかけで、seiyaさんに絡むとどうも対立してしまうので、できるだけ避けるように はしているのですが・・・・
(かみちゃん) 2009-03-27 15:36
こんにちは。かみちゃん です。
>>・は全角と想定しています。 > > なるほど。理解できました。seiyaさんありがとうございます。 > 全角／半角どちらなのでしょうね。＜JOJOさん
せっかくですから、勉強がてら 全角／半角どちらでも対応できるように考えてみました。
◆正規表現を使う方法
.Pattern = "(" & Replace(myPtn, "・","|") & ")"
の部分を
.Pattern = "(" & Replace(Replace(myPtn, "・", "|"), "･", "|") & ")"
とします。
なお、正規表現に興味があれば、いろいろ調べていただければいいのですが、たとえば、以下のようなところは、
わかりやすいかもしれません。
http://www.mnet.ne.jp/~nakama/
◆正規表現を使わなくてもいい方法
IsMonthIn = IsNumeric(Application.Match(Split(myMonth, ".")(1), _
Split(myPtn, "・"), 0))
の部分を
IsMonthIn = IsNumeric(Application.Match(Split(myMonth, ".")(1), _
Split(Replace(myPtn, "･", "・"), "・"), 0))
とすればいいかと思います。
いずれにしても、当たり前といえば、当たり前かもしれません。 全角の"・"か半角の"･"をどちらかに統一するということですので・・・ 上記の例では、全角の"・"に統一しています。
あとは、JOJOさんの返答待ちということにしたいと思います。
(かみちゃん) 2009-03-27 15:55
> MsgBox Join(Filter(Split("1･4･7･10", "･"), "1"))
> としたら、「1 10」と返ってきますので、完全一致というわけではないのですね。
> 勉強になりましたが、どれかひとつ一致していればいいと思っていますので、間違いではないと認識
> しています。
私はこの質問に対しての回答としては「間違い」だと認識しますし、 他人のコードを気にかけるより、「間違いではないと認識」するなら、 ご自分のコードの半角・全角はどうしたの？ (seiya)
こんにちは。かみちゃん です。
> 他人のコードを気にかけるより、
気にかけるのがいけないことなのですか？ ここは、「エクセルの学校」だから、勉強してもいいのではないでしょうか？ 勉強する価値はありそうだと思ったわけですし。
>> VBAの内容はよくわからないものの、A1セルから月を取り出さなくていいですね。
と、seiyaさんご提案のコードに一定の理解を示したつもりです。
> 「間違いではないと認識」するなら > ご自分のコードの半角・全角はどうしたの？
何をもって、間違いと指摘されているのが、「全く」気づいていないのですが、 全角と半角どちらでも動きます。
seiyaさんに「間違い」だと言われると、気になって仕方ないですね。 もう少し自分で考えたいと思います。（教えてくださいとは「あえて」言いません）
▼JOJOさん 一応動作確認をして、問題ないことは確認しているのですが、seiyaさんより > この質問に対しての回答としては「間違い」だと認識します と指摘をいただいているので、私のコードは使わないほうがいいのかもしれません。
原因と対処が判明するまで、一旦撤回することとします。
「間違っている」ようなコードを提示して、申し訳ありません。
(かみちゃん) 2009-03-27 16:57
わからいのかなーーーーー
A1 が H21.1 で
^^^
2 パターン 1・4・7・10 3・6・9・12 2・5・8・11 ←文字列で入力
^ ^ ^ ^^
だとしたら？
だから、私のコードを提示したまでです。 (seiya)
こんにちは。かみちゃん です。
> わからいのかなーーーーー
わかりませんね。
> A1 が H21.1 で > ^^^ > 2 パターン 1・4・7・10 3・6・9・12 2・5・8・11 ←文字列で入力 > ^ ^ ^ ^^ > だとしたら？
当然 Filterで戻ってくるのは、「1 10 12 11」 ですが、 1 が支給月として存在するからTRUEでいいのではと思っています。 ただ、seiyaさんがそこまで仰るからには、私の認識不足な点があるのかもしれません。
まぁ、seiyaさんのコードが正しいのでしょう。たぶん。 先ほども書きましたが、原因と対処がわからないので、私の案は、撤回させていただきます。
「間違い」と指摘を受けた以上、JOJOさんに使っていただくのは、申し訳ないからです。 とりあえず、seiyaさん、ご指摘ありがとうございます。 後は、自分で何がいけないのか、反省を兼ねて勉強いたします。
(かみちゃん) 2009-03-27 17:17
困ったもんだ... (seiya)
こんにちは。かみちゃん です。
> 困ったもんだ...
困らせてしまい申し訳ありません。 コード自体、撤回すると申し上げたつもりです。
私は、ご迷惑をおかけしたようですので、このスレッドから降りようと思います。
(かみちゃん) 2009-03-27 17:22
検証をして書き込みをしている間に、かなり更新されていてびっくりです。 「･」中黒は半角です。ちなみにそのパターンの数字も半角です。 シート名は半角ドットです。
実際の作業として、H22.4からH23.3と入力してやってみたところ、 H23.1とH23.2で全てを転記してしまう事象が発生しました。
とにかく貼り付けするくらいしか分からないので、週末に皆さんの書き込みの理解に 努めたいと思います。(JOJO)
どの関数を使用しているのか不明ですが,半角なら
=IsMonthIn(A38,$A$1)
Function IsMonthIn(ByVal myPtn As String, _
ByVal myMonth As String) As Boolean
IsMonthIn = IsNumeric(Application.Match(Split(myMonth,".")(1), _
Split(myPtn, "･"), 0))
End Function
(seiya)
こんにちは。かみちゃん です。
「スレッドから降りようと思います」と申し上げたところ、JOJOさんから書き込みがありましたので、少しだけ。
> 実際の作業として、H22.4からH23.3と入力してやってみたところ、 > H23.1とH23.2で全てを転記してしまう事象が発生しました。
seiyaさん提示のコードを半角に修正すると問題なく動いています。 （しつこいですが、私のコードでも問題なく動いていますが・・・）
結果的に、私がseiyaさんに対して、不快な思いをさせてしまったわけで、JOJOさんにもご迷惑がかかったのだと思いますが、
大変お手数ですが、 標準モジュールに記述したコードと、 A1セルの値 B38セル、B40セルの値、 交通費の行のセルの数式 を提示してみていただけませんか？
こちらで、検証することくらいはできます。 ただ、seiyaさんがまだいらっしゃるようですので、私は、黙って見守ろうと思います。
(かみちゃん) 2009-03-27 17:46
JOJOさん
話が混乱するので、私のコードを使用した場合は私に直接質問してください。 私がお答えします。
(かみちゃん)さん 頼むから、余計なことはしないでください.
お願いします!
(seiya)
こんにちは。かみちゃん です。
> 話が混乱するので、私のコードを使用した場合は私に直接質問してください。
そうですね。 そのため、JOJOさんに対して、 > 標準モジュールに記述したコード の提示をお願いしました。
> 頼むから、余計なことはしないでください
言いたいことはヤマほどありますが、そこまで仰るならば、後は、seiyaさんにお任せします。 よろしくお願いします。
JOJOさん、無事解決することを願っています。 邪魔だとハッキリ言われましたので、本当に撤退します。 「理解不足」と「間違っている？といわれているコードの提示」と「スレッドを汚してしまい」、ご迷惑おかけしました。
(かみちゃん) 2009-03-27 18:03
これって、マクロを使うまでも無い事だと思いますけど。。。。
=IF(ISERROR(FIND("･"&MONTH($A$1&".1")&"･","･"&B38&"･")),"",B40)
かみちゃんさんはなんだか納得して居られない様なので。。。 ＞当然 Filterで戻ってくるのは、「1 10 12 11」 ですが、 ＞1 が支給月として存在するからTRUEでいいのではと思っています。 今回は３パターン有ると仰って居られますね １． 1･4･7･10 ２． 3･6･9･12 ３． 2･5･8･11 検索月が「1」だった場合、1,10,12,11がヒットするので 全て表示されてしまいませんか？ こちらでは全て表示された状態に成りますが。
それから、検索月が「2」だった場合は 12,2がヒットするので ２のパターンと、３のパターンの人が表示されますよね。
かみちゃんさんがテストされたデータはどの様なデータでしょう？
(HANA)
こんにちは。かみちゃん です。
▼HANAさん
> 1,10,12,11がヒットするので全て表示されてしまいませんか？
本当ですね。やっと納得しました。このような指摘のされ方ですと、勉強になります。 1･4･7･10 のパターンだけしかテストしていませんでした。明らかなテストミスです。
的確なご指摘ありがとうございます。 seiyaさんにも、ご迷惑おかけました。申し訳ありませんでした。
> Filter Function は完全一致の検索はできません。
これがすべての誤りです。まだまだ勉強不足のようです。 私のコードをどう直したらいいかは、考えてみますが、明らかに間違いですので、使わないようにして ください。 seiyaさんが登場される直前の発言以降、私の発言をすべて削除したいくらいです。
ありがとうございました。
(かみちゃん) 2009-03-28 9:09
HANAさん、
私も、関数で処理できると思っていましたので（私には無理ですが...) [UDFなら] としておいたつもりです。
参考まで（これでも完全ではないでしょう,完全一致を求める意味で）
Function IsMonthIn(ByBal myPtn As String, _
ByVal myMonth As String) As Boolean
myMonth = Chr(1) & Split(myMonth, ".")(1) & Chr(1)
IsMonthIn = Len(Join(Filter(Split(Replace(myPtn,"･",Chr(1) & "･" & Chr(1)),"･"),myMonth)))
End Function
(seiya)
HANAさん、seiyaさん
色々な方法を有難うございました。 最終的に、馴染み深い!?関数で処理させていただきました。 思い通りになりました(#^.^#)
今回のことをきっかけとして、VBAにも少しずつ触れていきたいと思います。 かみちゃんさんも有難うございました。(JOJO)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.