【復活求む!】惜しくも解散してしまったバンド|J-ROCK編

名     番      号
a1111  22222   a1111s111
a1111  33333   a1111s112
a1111  44444   a1111t113
b1114  55555   c1111h114
b1114  66666   b1111s115
b1114  77777   b1111s116
a1111  88888   a1111h117
c1117  99999   c1111s118

この様な表で名列をa1111でオートフィル後に号列で"s"の個数を求めたいので、ご教示お願いします。マクロではなく関数でお願いいたします。

A 回答 (3件)

オートフィルタを使わない


G2: =SUMPRODUCT((A:A=E2)*(MID(C:C,6,1)=F2))
の方法では駄目ですか?
駄目なら、その理由は?
「エクセル関数 オートフィルタの関数を教え」の回答画像2
    • good
    • 0
この回答へのお礼

助かりました

ばっちりです。ありがとうございました。
一つ難点な事は扱っているエクセル行が80万行あって、この作業を行うと恐ろしく重くなります;;
でも勉強になりましたので、何かに役立てたいと思っております。

お礼日時:2019/04/17 08:46

済みませんが、[No.2]の添付図を此方のものに差し替えてください。

「エクセル関数 オートフィルタの関数を教え」の回答画像3
    • good
    • 0

こんばんは!



SUBTOTAL関数ではダメですか?
「号」の列はC列だとします
オートフィルタを掛けても非表示にならない行(たとえば1行目が項目行なら、1行目の使っていない)のセルに
=SUBTOTAL(3,C:C)-1

として、A列の「a1111」でフィルタ、次にC列のフィルタで
テキストフィルタ → 「指定の値を含む」を選択 → 指定条件の窓に「s」を入力しOK!

※ 単に表示されている行数(1行目の項目行の「1」はマイナス)が表示されるだけですので
フィルタを解除するとすべての個数+1 が表示されます。

的外れならごめんなさい。m(_ _)m
    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aと関連する良く見られている質問

Q日付の作成方法を教えて下さい。

縦カレンダーを作りたく、色々検索して月初日を作ることは出来ました。
そこから、+1してカレンダーを作りたいのですが上手く出来なかった為、質問させていただきました。


コード
Sub 日付作成()

Dim d As Date
d = Date ' 本日日付
Range("A2").Value = DateSerial(Year(d), Month(d), 1)

Dim tenkiws As Worksheet
Set tenkiws = Worksheets("転記先")

Dim i As Long
For i = 4 To 63 Step 2
tenkiws.Cells(i, 1) = tenkiws.Range("A2") + 1
Next i

End Sub

やりたいこととしては、一行飛ばしに日付を加算させていくなのですが、上のコードだと4行目だけ変わって後は変わりません。
どうすれば63行目まで変えることが出来るのでしょうか?
ご教授宜しくお願い致します。

縦カレンダーを作りたく、色々検索して月初日を作ることは出来ました。
そこから、+1してカレンダーを作りたいのですが上手く出来なかった為、質問させていただきました。


コード
Sub 日付作成()

Dim d As Date
d = Date ' 本日日付
Range("A2").Value = DateSerial(Year(d), Month(d), 1)

Dim tenkiws As Worksheet
Set tenkiws = Worksheets("転記先")

Dim i As Long
For i = 4 To 63 Step 2
tenkiws.Cells(i, 1) = tenkiws.Range("A2") + 1
N...続きを読む

Aベストアンサー

Sub 日付作成()

Dim d As Date
d = Date ' 本日日付
Range("A2").Value = DateSerial(Year(d), Month(d), 1)

Dim tenkiws As Worksheet
Set tenkiws = Worksheets("転記先")

Dim i As Long
For i = 4 To 63 Step 2
tenkiws.Cells(i, 1) = tenkiws.Range("A2") - 1 + i / 2
Next i

End Sub

Qエクセルの関数について

例年この時期に必ず苦労して困っています。質問させてください…
毎年毎年、販売個数を出すのに列を削除する方がいて、ミスが起きたり作り直しとか大変でとても困ってます。

列を削除してもSUMproductの関数がつかいたいのですが、

=SUMPRODUCT($C$3:(ADDRESS(3,(MATCH("枚数"),0)-1),1),C4:(ADDRESS(3,(MATCH("枚数"),0)-1),4)))

みたいな感じで考えて見たのですがエラーメッセージでお手上げです…

どうにか個数と書いたの手前の行までの掛け算をする数式ができないでしょうか

よろしくお願いします!

Aベストアンサー

又 訂正です、

=SUMPRODUCT(INDIRECT("C3:"&ADDRESS(3,MATCH("枚数",INDIRECT("C3:Z3"),0)+2)))

何度も 申し訳ない、
こっちですね。


そして応用編が、
=SUMPRODUCT(INDIRECT("C3:"&ADDRESS(3,IFERROR(MATCH("枚数",INDIRECT("C3:Z3"),0),COUNTA(INDIRECT("C3:Z3")))+2)))

此方は、
もし "枚数"記載列をも、
削除してしまっても、

まあ 取り敢えずは、
エラーには ならない、
式です。


確認くださいね。


後、
解説を ご所望に、
なられる場合は、

そう、
お申し付けくださいね。


追記、
いゃ〜、
楽しませて 頂きました、

久々に、
頭が 回る、
噛み応えの ある、
課題でした、

いい課題を 有り難うです。

QExcelのフィルター後のセルにコピー

エクセルでSHEET1にデータがあり 例えば 右横項目として 名前 年齢 出身地 あだ名項目(これはまだ未入力) など 横にならんでいて左端縦には下にいろんな人の名前がずらっと並んであった場合、例えば 県別項目の青森県でフィルターを掛けた場合 そのとき必ず6人いるとものして、SHEET2に用意していた、A列下のセルに縦に あだ名6個用意していたとする、たぬき、くま、きりん、ぞう、ウサギ、カラス それを6個まるごとフィルター後のSHEET1のあだな項目欄に一回でスポット コピーペーストできるものでしょうか。

Aベストアンサー

こんばんは!

一気に!という訳にはいきませんが、VBAでの一例です。
Sheet1の1行目は項目行でデータは2行目以降にあり、
D列が「あだ名」列になっているとします。
そして、Sheet2のA1セル以降に表示したいデータが羅列してあるという前提で・・・

Sub Sample1()
 Dim i As Long, cnt As Long
  With Worksheets("Sheet1")
   If .AutoFilterMode Then
    If .AutoFilter.FilterMode Then
     For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
      If .Rows(i).Hidden = False Then
       cnt = cnt + 1
       .Cells(i, "D") = Worksheets("Sheet2").Cells(cnt, "A")
      End If
     Next i
    Else
     MsgBox "絞り込まれていません"
    End If
   Else
    MsgBox "フィルタが設定されていません"
   End If
  End With
End Sub

とりあえずはお望みの動きになると思います。m(_ _)m

こんばんは!

一気に!という訳にはいきませんが、VBAでの一例です。
Sheet1の1行目は項目行でデータは2行目以降にあり、
D列が「あだ名」列になっているとします。
そして、Sheet2のA1セル以降に表示したいデータが羅列してあるという前提で・・・

Sub Sample1()
 Dim i As Long, cnt As Long
  With Worksheets("Sheet1")
   If .AutoFilterMode Then
    If .AutoFilter.FilterMode Then
     For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
      If .Rows(i).Hidden = False T...続きを読む

Q複数の条件に合うセルの個数を取得したい

複数の条件にあうセルの個数を取得し表示させたいです
フィルター以外の方法でやりたいので、ご教授お願い致します

↓に画像のファイルを置いています
https://drive.google.com/open?id=1lI0ylD1d2UKqzs7Aidknpz9YsDhKgde5

Aベストアンサー

こんばんは!

表の作りを工夫すれば簡単にできるかもしれませんが、
お示しの配置に極力近い形でやってみました。
ただ、↓の画像のように表の作りを変えて、作業用の列を2列設けています。

18行目
B・C列 → 20以上~25未満(年齢)
D・E列 → 25以上~36未満(年齢)
F・G列 → 36以上 
となります。

21行目 → 0以上~12未満(月数)
22行目 → 12以上~24未満(月数)
23行目 → 24以上~36未満(月数)
24行目 → 36以上
となります。

そして作業列H2セル(年齢用)に
=INDEX(B$18:G$18,MATCH(A2,B$18:G$18,1))
作業列I2セル(月数用)に
=INDEX(A$21:A$24,MATCH(G2,A$21:A$24,1))
という数式をそれぞれ入れフィルハンドルで下へコピーしています。

B21セル
=SUMPRODUCT(($H$2:$H$12=B$18)*($I$2:$I$12=$A21)*($B$2:$B$12="*")*(($D$2:$D$12="*")+($E$2:$E$12="*")))

C21セル
=SUMPRODUCT(($H$2:$H$12=B$18)*($I$2:$I$12=$A21)*($C$2:$C$12="*")*($F$2:$F$12="*"))

にそれぞれ上記の数式を入れ B21・C21セルを範囲指定 → C21セルのフィルハンドルで右へ2列ずつコピー!
そのまま下へコピーすると
画像のような感じになります。

※ 作業列が目障りであれば、遠く離れた列にするか
非表示にしてください。

※ 細かい検証はしていませんので、
お望み通りにならなかったらごめんなさい。m(_ _)m

こんばんは!

表の作りを工夫すれば簡単にできるかもしれませんが、
お示しの配置に極力近い形でやってみました。
ただ、↓の画像のように表の作りを変えて、作業用の列を2列設けています。

18行目
B・C列 → 20以上~25未満(年齢)
D・E列 → 25以上~36未満(年齢)
F・G列 → 36以上 
となります。

21行目 → 0以上~12未満(月数)
22行目 → 12以上~24未満(月数)
23行目 → 24以上~36未満(月数)
24行目 → 36以上
となります。

そして作業列H2セル(年齢用)に
=INDEX(B$18:G$18,MATCH(A2,...続きを読む

Qエクセルの関数について

エクセルの関数について、ご教示をお願い致します。
日々、sheet2のデータが送られて来るのですが、そのデータ(数値)をsheet1の表に自動へ変換するには、sheet1のC2:M25にはどのような関数を入力すれば宜しいのでしょうか?
悩んでも答えが出て来ません。どなたかご教示をお願い致します。

Aベストアンサー

添付図参照

Sheet1 において、

AI列を作業列として使用
式 =OFFSET($A$2,4*INT((ROW(A1)-1)/4),)
を入力したセル AI2 を下方にズズーッとオートフィル

G/標準;G/標準;
に書式設定し、かつ、次式を入力したセル C2 を右方および下方にズズーッとオート委フィル
=SUMPRODUCT((Sheet2!$A$3:$A$200=$AI2)*(Sheet2!$B$3:$B$200=C$1)*(Sheet2!$C$2:$F$2=$B2)*(Sheet2!$C$3:$F$200))

QIF関数とVLOOKUP関数で複数条件を組み合わせたい IF(VLOOKUP(J5,リスト!$E:$

IF関数とVLOOKUP関数で複数条件を組み合わせたい

IF(VLOOKUP(J5,リスト!$E:$F,2,FALSE)=O$4,1,IF(N5>0,0.5,0.25)

上記の数式を下記の複数条件を組み合わせた数式にしたいです。どのように書き換えたらよいでしょうか?

①リストで検索した数字がO4と合致して、かつN5>0だったら100
②リストで検索した数字がO4と合致せず、かつN5<だったら25
③リストで検索した数字がO4と合致して、かつN5<0だったら75
④リストで検索した数字がO4と合致せず、かつN5>0だったら50

Aベストアンサー

済みません。


式を 謝罪の上、
訂正させてください。


現行、
=IF(N5>0,
 IF(VLOOKUP(J5,リスト!$E:$F,2,FALSE)=O$4,
  100,
  25,
 )
 IF(VLOOKUP(J5,リスト!$E:$F,2,FALSE)=O$4,
  75,
  50
 )
)


改訂後、

=IF(N5>0,
 IF(VLOOKUP(J5,リスト!$E:$F,2,FALSE)=O$4,
  100,
  25,
 ),
 IF(VLOOKUP(J5,リスト!$E:$F,2,FALSE)=O$4,
  75,
  50
 )
)
             以上、

申し訳ない。

Qエクセル データの入力規制「リスト」でこんな事できますか?

エクセルでこんな事できますか?
並列したQ列 R列があり、
また、添付には表記されていませんが、
その隣にS列 T列があり、
Q列、S列は、「コード」という事で、
Q列は1,2,3,4,5,6,7,8,9,10
S列は11,12,13,14,15,16,17,18,19,20
です。
R列は、「材質」でQ列に対応し、添付ファイルのように10項目
T列も「材質」でS列に対応し、10項目あります。

R列 T列「材質」を見て Q37セルにコードを入力したく、
その時、ドロップダウンリストとして、R列、続けて、T列の項目を表示したいです。

データの入力規制「リスト」で、できそうかと思い、頑張っていたのですが、
うまくいかず困っています。
データの入力規制「リスト」にはこだわりませんが、
マクロはできるだけ使わず完成したく思います。
エクセル詳しいかたご教授よろしくお願いします。

Aベストアンサー

あー…
入力規則はあくまでも、データ入力を補助するための機能ですからリストと違う文字を反映させることはできませんよ。

例えばリストデータは、01りんご・02プリンのように作って置いて、コードを出す時に
=LEFT(Q37,2)*1
と入れておくとか?

Q【エクセル】指定した日付に一番近い日付を抽出する関数について

エクセルの関数について質問です。


A____B_______
佐藤 2011/5/2
佐藤 2011/8/25
佐藤 2012/1/8
山田 2011/6/7
山田 2012/2/13
高橋 2010/7/10
高橋 2010/11/1
高橋 2010/12/5
高橋 2011/2/10

というデータから、




A____B________
佐藤 2011/5/1
佐藤 2011/9/1
山田 2011/6/1
山田 2011/8/1
高橋 2011/1/1
高橋 2011/2/1


のデータの日付に一番近い日付を
①のデータから抽出する関数を教えて頂きたいです。
指定日に一番近い日付を、
未来と過去から抽出する関数です。

分かりづらくて申し訳ございません。
ご教示願います。
よろしくお願いいたします。



iPhoneから送信

Aベストアンサー

もし、
D1に 入力された、
日と 同じ日を、
抜き出したいのなら、
此ですかね?


ファイル、
https://1drv.ms/x/s!AjviygfJDgV_3CyTu_uDNnR5Xlwk


尚、
ファイルは 必ず、
エクセルで 一度、
開き、

ローカルに 別名保存を、
してくださいね、

別名保存でないと、
意味が 無いですよ。


そうすれば、
閲覧も、編集も、
可能に なると、
思います。

Qエクセルの横検索(関数)について

エクセルの関数で、縦検索語に、横に伸びたデータの最終セルが知りたいのです。

シート1のA行にある文字列や数字を参照し、
シート1のB列にシート2を参照して最終の数字が
入ってる日付をしりたいのですが、
どなたか教えていただけませんでしょうか(>_<)

関数を入力するセルはシート1のB列になります。

シート1
  A  B  C  D  E
1 あ 1/4
2 い 1/3
3 う 1/3
   ↑ここを出したい

シート2
  A  B  C  D  E
1   1/1 1/2 1/3 1/4
2 あ  1  2  3  4   
3 い  5     6  
4 う     9  10 


VLOOKやMATCH、INDEX、HLOOKUP等で色々考えていたのですが
まったく思いつきません(>_<)
VBAの使用はできませんので、出来れば関数で組みたく思っております(>_<)

何卒、お力をお貸しください(>_<)

Aベストアンサー

こうですかね?


ご心配なく、
割と 造作も、
ない事ですから、
お悩みに ならずとも、
構いませんよ、

判れば 簡単です。


式、
=OFFSET(Sheet2!$A$1,0,MAX(INDEX(COLUMN(OFFSET(Sheet2!$A$1,1,1,1,500))*ISNUMBER(OFFSET(Sheet2!$A$1,MATCH(A1,Sheet2!$A$2:$A$20,0),1,1,500)),,))-1,1,1)


ファイル、
https://1drv.ms/x/s!AjviygfJDgV_3EPM2ZlNxp7ZQALn


尚、
ファイルは 一度、
ローカルに 別名保存し、
其の 保存ファイルを、
扱うように してくださいね、

此の時、
別名保存でないと、
意味が 無いですよ。


さすれば、
閲覧も、編集も、
叶うものと 思います。


さて、
此を使うなら、
ブラックボックスとして 扱わず、
「こんなものだ」とは 見ず、

ちゃんと 理解して、
くださいね。


私達は 作り方を、
お教えしている、
つもりで あって、

下請けの つもりでは、
ないのです、

ご理解くださいね。


ではでは、

基本から。


エクセル論理演算の場合、
戻り値(返される値)は、
TRUE、FALSE、
此の 2値で、

各々、
TRUEは 0以外と、
FALSEは 0と、

演算時に 型不一致が、
あれば、

内部的に 読み替えが、
されます。


基本的に、
此の 性質を、
利用します。


さてさてさて、
Excelでは、
数値か、否かは、
ISNUMBER構文でも 良いでしょう、

他でも 良いのですがね、
便利ですから。


例えば、
シート2 2列目を、
数値か 見させると、
B2:E2
まで 数値で、
TRUE、
其れ以降が 空セルで、
FALSE、
と 教えてくれます。


先にも 挙げた通り、
TRUE、FALSE、
は、

各々、
0意外、0と、
見なされるのですよね?

其処で 其れ等の、
戻り値に、
COLUMN構文を 使って、
列番号連番を 生成し、
掛け合わせています。


すると、
TRUEが 返された場所では、
列ナンバーが 残り、
FALSEか 返された場所では、
0が 残されます。


列ナンバーは、
何も 操作しなければ、
必ず 0より、
大きいので、

残された 列ナンバー内で、
最大のものを 選り出せば、

其の列が 数値記載最右列と、
判る訳です。


で、
最大を 求めるのは、
MAX(INDEX(……,,))構文です、

……の場所に、
最大値を 得たい式を、
いれます。


もう此処までで、
シート2の、日付記載行の、
何列目を 読み出したら、
良いか、
が 判った訳です、

意外と 簡単でしょ?


後は、
今までで 位置特定か、
済んだ、
意図した 位置を、

OFFSET構文で 読み出せば、
良いだけです。


如何ですか?
案外 造作も、
ないでしょ?

こうですかね?


ご心配なく、
割と 造作も、
ない事ですから、
お悩みに ならずとも、
構いませんよ、

判れば 簡単です。


式、
=OFFSET(Sheet2!$A$1,0,MAX(INDEX(COLUMN(OFFSET(Sheet2!$A$1,1,1,1,500))*ISNUMBER(OFFSET(Sheet2!$A$1,MATCH(A1,Sheet2!$A$2:$A$20,0),1,1,500)),,))-1,1,1)


ファイル、
https://1drv.ms/x/s!AjviygfJDgV_3EPM2ZlNxp7ZQALn


尚、
ファイルは 一度、
ローカルに 別名保存し、
其の 保存ファイルを、
扱うように してくださいね、

此の時、
別名保存でないと、
意味が ...続きを読む

Qエクセルの関数教えてください

隣のシートから
=Sheet2!A3 の式でセルの内容を表示したいのですが
その際に例えば表示された内容が
レッドりんごだとすると、それを赤リンゴと書き換えて表示したいのですが
関数で対応可能でしょうか?
宜しくお願い致します。

Aベストアンサー

=SUBSTITUTE(Sheet2!A3,"レッドりんご","赤リンゴ")
んー、現実的ではないなぁ。
対比表が別シートにあってvbaで検索かけたほうが現実的かな。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング

価格.com 格安SIM 料金比較