[過去ログ] Excel総合相談所 140 (962レス)
1-

このスレッドは過去ログ倉庫に格納されています。
次スレ検索 歴削→次スレ 栞削→次スレ 過去ログメニュー
1 2020/07/21(火) 08:00:43
【1 OSの種類         .】 Windows**
【2 Excelのバージョン   】 Excel**
【3 VBAが使えるか    .】 はい・いいえ
【4 VBAでの回答の可否】 可・否

▼━関連スレ━━━━━━━━━━━━

前スレ
Excel総合相談所
https://find.5ch.net/search?q=excel+%E7%9B%B8%E8%AB%87%E6%89%80

Excel VBA 質問スレ
https://find.5ch.net/search?q=excel+vba

【質問不可】Excel総合相談所スレの雑談・議論スレ
https://find.5ch.net/search?q=excel+%E3%80%80%E7%9B%B8%E8%AB%87%E6%89%80
2 2020/07/21(火) 08:21:42
関数を使って集計する方法を教えていただけないでしょうか。
index matchの組み合わせもしくは、sumifもしくはindex match sumifの複合で表示させたいです。

画像の出荷と書かれたセルに関数を入れて集計したいです。
集計は参照シートの黄色で書かれた「販売+その他出荷」の合計を表示したいです。
sumifでは縦の計算になってしまい、横の足し算が出来ない印象です。
上の数字はmatchを使った際の引用条件になると思い入れました。

説明わかりづらくてすいません。関数を教えていただけないでしょうか。
具体例のエクセルは簡単に書いてますが、実際に書くエクセルはデータが膨大なものです。
なぜかindexもうまく引用できていませんでした。


https://d.kuku.lu/ca513f39a8
3 2020/07/21(火) 09:11:18
>>2
前スレの232の方ですね。自分を含む数名から回答を受けて解決したのではありませんか?
4 2020/07/21(火) 12:58:01
VBAで質問です。

何十列もある表で不要な列を大量に削除したいと思っています。

Range("A1,N1:U1.....").entirecolumn.deleteといった感じで手作業、つまり目で列を確認してコードを書くしかないのでしょうか??

分かる方お願いします。
5 2020/07/21(火) 13:00:46
いやそんなことはない
でも何十くらいなら目チェックの方が早いし安いな
6 2020/07/21(火) 14:13:59
それでいいので教えてください。
どうやって不要の列を決め打ちするので?
7 2020/07/21(火) 15:29:53
列削除だけのコード書くくらいなら手動で消していけばいいじゃん に対してのそれで良い なのか
いやそんなことはない に対してのそれで良いなのか
8 2020/07/21(火) 17:25:11
使用していない行に1とかフラグを置いて、union使って列指定
この辺を参考に
https://mmm-program.com/vba-delete-line-fast/
9 2020/07/21(火) 18:44:44
それだと不要な列を目でみて1をつけないとならないので意味がナッシングです。
10 2020/07/21(火) 18:53:25
不要な列の条件が分からないのにどうしろと
11 4 2020/07/21(火) 18:57:20
>>10

> 不要な列の条件が分からないのにどうしろと


表に見出し(1行目)があるのでそれを見ながら削除しています。あ、この見出しの列は不要と判断します。不要な見出しをリスト化したら可能とかありますか?
12 2020/07/21(火) 19:33:37
不要な見出しリストが作れるならわりと簡単
13 2020/07/21(火) 19:36:09
目で見て比較しないとわからないような曖昧なリストしか作れない場合は無理
14 4 2020/07/21(火) 19:53:11
>>12
教えてください!お願いします!!
15 2020/07/21(火) 21:05:38
いいですよ!
16 15 2020/07/21(火) 22:13:37
>>2>>14
様、大変長らくお待たせ致しました
以下がコードになりますのでご査収下さいませ
よろしくお願い致します



Sub Macro1()
不要な列を削除するための見出し = "aaa"
最終列 = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 最終列 To 1 Step -1
If Cells(1, i).Value = 不要な列を削除するための見出し Then
Columns(i).Delete Shift:=xlToLeft
End If
Next
End Sub
17 4 2020/07/21(火) 22:20:11
>>16
ありがとうございます。
試してみたいと思います。
18 2020/07/21(火) 22:59:32
【1 OSの種類         .】 Windows10
【2 Excelのバージョン   】 Excel2019
【3 VBAが使えるか    .】 いいえ
【4 VBAでの回答の可否】 否
1つの列にデータがずらっと並んでいて、そこから重複していないデータだけを別のシートに抽出する方法を教えてください
りんご
りんご
バナナ
みかん
みかん
が並んでいる場合、バナナだけを抽出したいです
19 2020/07/21(火) 23:08:54
>>18
作業列を作る
ifとcountifで重複ありを0、重複なしを1
もう1列作業列を作る
ifで1の場合rowを返す
もう1列作業列を作る
rowをrank付けする

別シートの列に
1
2
3
4
とか入力する

indexとmatchでrankを参照
20 2020/07/21(火) 23:10:14
>>18
countifで1を出す
その後、フィルタで抽出してコピーペースト
https://i.imgur.com/ewsZgaG.png
21 2020/07/21(火) 23:18:32
if(countif(A:A、A1)=1、MAX($B$1,B1)+1、“”)
で上から1、2、3…って数字つける
それからIndex MATCH
22 2020/07/21(火) 23:20:49
被ったし回答し直すか、と思ったらまた被って俺は
https://i.imgur.com/BDIp5Ig.png

ただこのやり方ってなんとなくindexmatchがなんとかなりそうな気はする
23 2020/07/21(火) 23:22:21
ルックアップでりんごとかのカズ数えて一個しかない奴を再度ルックアップすれば良い
24 2020/07/21(火) 23:22:59
もっとよくやるのは
countif($A$1:A1,a1)
で初登場を集める方法
25 2020/07/21(火) 23:23:33
すみませんが、VBSはちょっと、、、
VBAを使わない方法はないでしょうか。
26 2020/07/21(火) 23:25:28
>>25
ビデオもマクロも使ってない
27 2020/07/21(火) 23:27:12
>>25
>>4の方?

・全体をコピーして行列を入れ替えて貼り付け
・元見出しだった1行目はA列になるので、A列で不要な見出しでフィルタリング
・フィルタしたものを削除し、フィルタ解除
・もう一回行列入れ替えて貼り付け

これ以上は難しいかな
28 4 2020/07/21(火) 23:45:52
>>27
すごい。

明日試してみたいと思います。ありがとう。
29 2020/07/21(火) 23:56:29
>>18です
方法教えてくれてありがとうございます
作業列作って計算するしかなさそうですね
365のunique関数が欲しい…
30 2020/07/22(水) 00:40:46
作業列無しで関数で出す方法もあるけどメタクソ長い関数になるから作業列作ったほうが楽
31 2020/07/22(水) 01:07:12
>>18
2007以降ならリボンのデータから重複の削除アイコンかある
32 2020/07/22(水) 04:10:55
>>31
リンゴとか2つ以上あるやつはリンゴ1つにするのではなく、無視する方法を知りたいようだぞ
33 4 2020/07/22(水) 05:20:24
>>16

> >>2>>14
> 様、大変長らくお待たせ致しました
> 以下がコードになりますのでご査収下さいませ
> よろしくお願い致します
>
> 記
>
> Sub Macro1()
> 不要な列を削除するための見出し = "aaa"
> 最終列 = Cells(1, Columns.Count).End(xlToLeft).Column
> For i = 最終列 To 1 Step -1
> If Cells(1, i).Value = 不要な列を削除するための見出し Then
> Columns(i).Delete Shift:=xlToLeft
> End If
> Next
> End Sub


すみません、1行目の不要な列を削除するための見出しが1個だけではなく何個もある場合はどうしたらいいでしょうか?
34 2020/07/22(水) 05:32:34
>>33
select case
35 2020/07/22(水) 05:41:52
select Case cells(1,i).value
case"aaa","bbb"
columns(i).delete shift:=xlToleft
end select
36 2020/07/22(水) 07:40:29
>>35
>select Case cells(1,i).value
>case"aaa","bbb"
>columns(i).delete shift:=xlToleft
>end select

見出しのリストも自動で作りたいのですが、可能で紹介?
37 2020/07/22(水) 08:00:41
>>36
見出しのリストがどんなもんかわからん
ccc
ddd
のようなリストがほしいだけなら見出し行をコピーして行列置換でペーストすればいいだけだぞ
38 2020/07/22(水) 09:11:07
>>37
>>>36
>見出しのリストがどんなもんかわからん
>ccc
>ddd
>のようなリストがほしいだけなら見出し行をコピーして行列置換でペーストすればいいだけだぞ

上のコードを動かすために見出しのリストが必要なんですかい。
39 2020/07/22(水) 09:42:51
よくこんな釣りに構ってられるなお前ら
40 2020/07/22(水) 12:05:56
禿同
41 2020/07/22(水) 12:08:19
お前らも黙ってられないのか?
42 4 2020/07/22(水) 12:38:02
>>35

実行できました。ありがとうございます。
43 2020/07/22(水) 13:13:36
【1 OSの種類         .】 Windows10
【2 Excelのバージョン   】 Excel2007
【3 VBAが使えるか    .】 いいえ
【4 VBAでの回答の可否】 否

A1〜F1のセルには1〜4いずれかの数字が入ります
G1のセルには自分の指定した数字(10〜20の範囲内)を入れます
G1の値になるようにA1〜F1の組み合わせを全て書き出す方法を教えて下さい
44 2020/07/22(水) 13:16:06
1ファイル12 Sheetで一年間のデータを管理しています。
1月〜12月がそれぞれ1Sheet〜でSeet1月〜Sheet12月、Sheetは1日〜月末まで日々のデータです。

これとは別に同ファイルに、1Sheetに1月〜12月までの表を12ヶ月分並べたもの(Sheet年間)があるのですが
月ごとにデータを入力したら、こちらのSheetの、該当日にも入力されているようにするにはどうやればいいのでしょうか?
45 2020/07/22(水) 13:17:42
書き忘れました
【1 OSの種類         .】 Windows10
【2 Excelのバージョン   】 Excel 2016
【3 VBAが使えるか    .】 いいえ
【4 VBAでの回答の可否】 否
46 2020/07/22(水) 13:32:31
>>43
何をどう組み合わせてG1の値にしたいのか例を書いてくれ
47 2020/07/22(水) 14:09:13
【1 OSの種類         .】 Windows10
【2 Excelのバージョン   】 Excel2016
【3 VBAが使えるか    .】 いいえ
【4 VBAでの回答の可否】 可

以前下記のExcelからメール作成するフォーマットをここで教えてもらい便利に使ってました
マクロ実行でB4がTO、B5がCC、B6が件名、B7が本文に入力されてメール作成画面が開きます

ところが会社の中国人が使いたいと言うので渡したところ
中国語のWindows10だと件名と本文が文字化けします
中国語等の外国語のWindows PCでも文字化けしないようにできますでしょうか

Sub createMail()
CreateObject("Wscript.Shell").Run _
"mailto:" & URLEncode(Range("B4").Value) & _
"?cc=" & URLEncode(Range("B5").Value) & _
"&subject=" & URLEncode(Range("B6").Value) & _
"&body=%FF%FE" & URLEncode(Range("B7").Value)
End Sub

Function URLEncode(ByVal strOrg As String) As String
With CreateObject("ScriptControl")
.Language = "JScript"
URLEncode = .CodeObject.encodeURI(strOrg)
End With
End Function
48 2020/07/22(水) 14:46:59
>>46
ありがとうございます
最低でもA1〜F1には1以上の数字を必ず入れます
G1は(A1+B1+C1+D1+E1+F1)の合計になります
例えばG1が11の場合、(A1+B1+C1+D1+E1+F1)のパターンを全て知りたいです
49 2020/07/22(水) 15:26:37
すみません、解決しました
以前教えてもらったMODとROW関数とフィルタで出来ました
50 2020/07/22(水) 17:11:54
【1 OSの種類         .】 Windows10
【2 Excelのバージョン   】 Excel2013
【3 VBAが使えるか    .】はい
【4 VBAでの回答の可否】 可

色んなデータがあるサイトでピンポイントで一つのデータをスクレイピングしたいんですが、
そのデータがソースで毎回30行目で固定されてるんで、
行指定でデータ吸い取れたら良いんですが、やり方がわかりません。
お願いします。
51 2020/07/22(水) 17:24:27
>>50
HTMLソースを文字配列に読み込んで30番目を使う
52 2020/07/22(水) 20:49:56
>>43
ゴールシークという機能を使う
53 2020/07/22(水) 21:39:14
>>35
caseの後のaaa、bbb………を別のシートにリスト化のような感じになっていたらcaseのあとに全部書く必要はなくないでしょうか?やり方を教えてほしいで。、
54 2020/07/22(水) 22:00:00
>>53
worksheetfunction.matchと
case is 構文
エラー処理も必要
55 2020/07/22(水) 22:22:31
>>47
中国語の環境がないから何とも言えないけど・・・
UTF-8になっているから逆に文字化けするのがおかしい気がする
メールソフトがutf-8に対応していないと思う。多分
メールソフトを変えてみると解決するかもしれない
メールソフトを帰るのが無理だと、中国語(euc-cn)などにエンコードすればいいかもしれないけど、すげー大変そう

>>53
sheet1のa1:a5がリスト。
findの返り値はsetしないと使えない事に気づくのに3分かかったわ

Sub Macro1()

最終列 = Cells(1, Columns.Count).End(xlToLeft).Column

For i = 最終列 To 1 Step -1
不要な列を削除するための見出し = Cells(1, i).Value

Set result = Sheets("sheet1").Range("a1:a5").Find( _
What:=不要な列を削除するための見出し, LookAt:=xlWhole)

If Not (result Is Nothing) Then
Columns(i).Delete Shift:=xlToLeft
End If
Next

End Sub
56 2020/07/22(水) 23:09:25
>>55
どこでUTF-8になってると分かるのでしょうか?
57 2020/07/22(水) 23:19:57
>>56
テキトーな文字をエンコードして、それで検索したら大体分かるよ

考えてみたけど、中国語への変換は中国人に中国語で検索してもらうと見つかるかもしれない
58 2020/07/23(木) 08:11:32
>>47
ユニコードには表現方法がいくつもあって、ワークシートの文字列をVBAで扱う時はUCS-2形式のユニコードが使われるから、これをUTF-8形式に変換しないと基本的には文字化けする
具体的な方法は知らんけど、ADODB.StreamオブジェクトのCharsetプロパティでできるかもしれない
59 2020/07/23(木) 08:39:15
>>52
ありがとうございます
60 52 2020/07/23(木) 09:24:34
>>55
ありがとう。
61 2020/07/23(木) 09:34:12
ワークシート上の文字列をUTF-8に変換する関数
これでも文字化けするなら、さらに16進数に変換しないとだめかも

Function UTF8(text As String) As String
  With New ADODB.Stream
    .Open
    .Type = adTypeText
    .Charset = "UTF-8"
    .WriteText text
    .Position = 0
    .Type = adTypeBinary
    .Position = 3
    UTF8 = .Read
    .Close
  End With
End Function
62 2020/07/23(木) 09:36:40
補足
ADODB.Streamを使う時は参照設定で「Microsoft ActiveX Data Objects x.x Libraryを追加
バージョンは2.5以上ならどれでもいいはず
63 2020/07/23(木) 17:22:53
> "&body=%FF%FE"
BOMだけパーセントエンコードがUTF-16LEになってる
64 2020/07/23(木) 19:10:30
>>47です

>>57
すみません意味分かりません

>>58
>>61-62
その方法だと使うPC全てExcelのVBAでADODB.Streamオブジェクトを
使えるようにしなければなりませんよね?

>>63
そこを下記のようにsubjectの行と同じように変更し
自分の日本語Windows PCで中国語でニーハオとB6とB7に入力して
実行したら件名は文字化けしないけど本文だけ文字化けしました

"&subject=" & URLEncode(Range("B6").Value) & _
"&body=" & URLEncode(Range("B7").Value)

元々の
"&body=%FF%FE" & URLEncode(Range("B7").Value)
だと本文も文字化けしません
65 2020/07/23(木) 20:28:24
ならそれでもう使えるだろ?
目的を明確にしてから質問してくれよな
66 2020/07/23(木) 23:50:57
VBAでSUBTOTAL関数を使って2つの列の合計を別のセルに出したいです。非表示部分も合計されて出てしまいます。可視セルのみの合計を出したいです。よろしくお願いします。
67 2020/07/24(金) 01:20:03
>64
結果報告とはありがたい

>>66
オートフィルタがないとうまく動かないかもしれない
https://i.imgur.com/9xwKziG.png
68 2020/07/24(金) 09:31:26
>>66
アグリゲートにしたら?
69 2020/07/24(金) 10:24:16
どうやって?
70 2020/07/24(金) 10:40:35
アグリゲートってなんだ?
71 2020/07/24(金) 11:13:16
そこは自分で調べて理解しないと。
72 66 2020/07/24(金) 11:55:05
<<67
オートフィルターメソッドのことですか?一応オートフィルターメソッドは使ってます。ありがとうございました。

<<68
初めて見る関数です。調べてみたいと思います。ありがとうございました。

なにぶん初心者なんで分からない事だらけで困ってます。
73 66 2020/07/24(金) 11:56:00
>>72
不等号の向きを間違えました。すみません。
74 2020/07/24(金) 12:51:57
>>70
サブトータルよりアグリゲートよく使ってるわ
75 2020/07/24(金) 16:13:54
100日後に死ぬアグリゲート
76 2020/07/24(金) 21:38:40
sumproductはオワコンなのか?
77 2020/07/24(金) 21:42:00
>>76
オワコンって訳じゃないけど
・範囲が見えない、うまく使わないと重くなりやすい。
・範囲が見えない、計算ミスの可能性が出やすい
・計算式が複雑になると見づらくなる

そもそも作業列の方が使い勝手がいいかなーとは思う
78 2020/07/24(金) 21:57:51
>>77
めちゃくちゃ重いじゃん
79 2020/07/24(金) 22:04:45
>>78
そこはケースバイケース
例えば頻度にしても毎日使うならみやすさ犠牲にしてsumproduct()にしても良い
重くても月1なら我慢しても良い
個人的には見出しがある方が使い勝手が良い
重くてもミスが減るならPCを買い替えてもらうという選択肢もあるし、
多少ミスってもいいような資料ならPCは買い換えられないなど
更に言うならクラウドにするという選択肢もアリか

どちらが良いというものではないね
80 2020/07/24(金) 22:11:37
=SUMPRODUCT((○○=&#9747;&#9747;)*(□□=△△),●●)
みたいなのはSUMIF INDEX MATCHの方がいいよね
81 2020/07/24(金) 22:15:21
>>80
そうそう、多少重さは犠牲にしても見やすさを重視する方が良いと思う
SUMPRODUCTをよほど極めてるならSUMPRODUCT一本でもいいと思うけど、俺には無理だ
82 2020/07/24(金) 22:25:38
>>80
どうやんのそれ?
83 2020/07/24(金) 23:13:27
http://imgur.com/1QQj1LZ.png
http://imgur.com/5ZzdK8f.png
84 2020/07/24(金) 23:44:05
sumifs(offset(MATCH、
のほうが追いかけられて好き
85 2020/07/24(金) 23:58:39
>>84
高さがいつもわけ分からなくなる
86 2020/07/25(土) 00:06:59
>>85
表の最後に /END っていうセルをいれておいてMATCHでサイズを計る
87 2020/07/25(土) 00:55:45
>>86
こういう事?
offsetってイマイチわからないわ
行数と幅は省略でもよさそうだけど
http://imgur.com/5SH4A7n.png
88 2020/07/25(土) 01:03:21
高さもSUMIFの範囲で指定してあるから省略でもよくね?
89 2020/07/25(土) 01:03:24
>>87
そんな感じ
条件の範囲もoffsetにしとけば
表のサイズが変動しても対応してくれる
90 2020/07/25(土) 01:10:19
SUMPRODUCTじゃダメなん?
=SUMPRODUCT(($B$3:$B$12=$B15)*INDEX($C$3:$G$12,,MATCH(C$14,$C$2:$G$2,0)))
91 2020/07/25(土) 01:11:36
>>90
重いんだもん
92 2020/07/25(土) 01:20:10
B:B、2:2
みたいな指定で、表のサイズが変わっても使えるようにしたい
93 2020/07/25(土) 01:46:18
>>92
そうそう
そうしたくて色々やってるけど酔っ払ってて頭こんがらがってきた
出来てんのか出来てないのかわからん
SUMIFの合計範囲のOFFSETのMATCHのOFFSETのMATCHとか頭おかしくなるわ
http://imgur.com/2RO6kwE.png
94 2020/07/25(土) 02:06:15
何で表のサイズに縛られないテーブルの構造化参照を使わないの?
95 2020/07/25(土) 02:17:01
>>83の2枚目をテーブル化でいいじゃん
96 2020/07/25(土) 09:41:05
色々書いてもらって悪いが、酔っ払いはとりあえず消えてくれw
この質問に回答できる人よろしく

>>82
>>>80
>どうやんのそれ?
97 2020/07/25(土) 10:08:47
>>96
書いてあるが
98 2020/07/25(土) 12:15:40
合ってるかどうかはしらんけど>>93が大体それだろう
ただこんなややこしい式は真似しないほうがいいと思う
99 2020/07/25(土) 13:11:31
ユーザーフォームを呼び出すシート(Sheet1)のコンボボックスに
同ブック上別シート(Sheet2)のA1:A30項目をプルダウン表示させたいのですが
その際、重複項目と空欄を省いた状態で表示させるコードを教えて頂ける方いませんでしょうか

初心者には見当もつかず よろしくお願い致します
100 2020/07/25(土) 13:47:09
いいけど、何をやりたいのか、その説明ではわからないので、書き直してもらえますか?
1-
あと 862 レスあります
スレ情報 赤レス抽出 画像レス抽出 歴の未読スレ AAサムネイル

ぬこの手 ぬこTOP 0.622s*