Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows XP Home Edition : Excel 2002)
頭部に◎印をつけた名前のみリスト表示したい
投稿日時: 18/01/03 12:17:13
投稿者: ISBSGR

毎度お世話になります。下記の通り、<記号>列欄に◎印を付けた行の、<名前>列欄の名前を別の列に抽出していますが、下記の配列数式では、◎印を付けた分は抽出されますが、それ以下に入力されている数式はエラーになるので、ISERROR関数を使ってエラー処理していますが、長が〜い数式になってしまいます。もっとスマートな数式がありそうな感じがしているのですが、よろしくお願いします。
 
  <記号>   <名前>     <抽出後の名前>
4  ◎     リンゴ      リンゴ
5       バナナ       柿
6  ◎     柿         ブドウ
7       みかん       #NUM!
8  ◎    ブドウ       #NUM!
9                #NUM!
10      ビワ        #NUM!
 
 
   {=INDEX(名前,SMALL(IF(記号="◎",ROW(記号)),ROW()-3)-3)}

回答
投稿日時: 18/01/03 12:38:29
投稿者: んなっと

<名前>がB列の場合。
 
   A    B    C
 4 ◎ リンゴ リンゴ
 5   バナナ   柿
 6 ◎   柿 ブドウ
 7   みかん    
 8 ◎ ブドウ    
 9          
10    ビワ    
 
C4
=INDEX(B:B,SMALL(INDEX(ROW(記号)+(記号<>"◎")*10^4,),ROW(1:1)))&""
下方向・↓
 
※前回の質問もそうでしたが、このような抽出を関数のみでやる場合は作業列を使うのがおすすめです。
 
右のほうあいているところ(下の例ではK列)に作業列
 
K4
=K3+(A4="◎")
下方向・↓
 
     A    B    C D E F G H I J K
 3 <記号> <名前> <抽出>            
 4   ◎ リンゴ リンゴ            1
 5     バナナ   柿            1
 6   ◎   柿 ブドウ            2
 7     みかん                2
 8   ◎ ブドウ                3
 9                        3
10      ビワ                3
 
C4
=IF(ROW(1:1)>COUNTIF(記号,"◎"),"",INDEX(B:B,MATCH(ROW(1:1),K:K,0)))
下方向・↓

回答
投稿日時: 18/01/03 13:26:17
投稿者: Mike

{=IFERROR(INDEX(名前,SMALL(IF(記号="◎",ROW(記号)-3),ROW(A1))),"")}

回答
投稿日時: 18/01/03 13:30:23
投稿者: んなっと

前回の質問と同じく Excel 2002 ですね。

回答
投稿日時: 18/01/03 15:32:22
投稿者: Mike

んなっと さんの引用:
前回の質問と同じく Excel 2002 ですね。
またまた私の FAULT でしたネ。m(_._)m
ではでは、式 =ISERR(C4) を満たす場合にフォント色を白とする[条件付き書式]を設定したセル C4 に、目障りだったエラー対策用の「IFERROR(,"")」を取っ払った次式を入力した後で、当該セルを好きなだけ下方にズズーッとオートフィルするってのが「スマート」かと。→ ISBSGRさん
 
{=INDEX(名前,SMALL(IF(記号="◎",ROW(記号)-3),ROW(A1)))}

回答
投稿日時: 18/01/03 16:30:30
投稿者: WinArrow
投稿者のウェブサイトに移動

数式で対応することが必須なのかしら?
 
オートフィルタを使う・・・て対応もあると思いますが・・・・

回答
投稿日時: 18/01/04 00:59:46
投稿者: LMK

こんばんは
やはり気になるのは、マニアック過ぎる方法では将来誰もメンテできなくなるだろうということです。
 
仮に自分が誰かからブックを引き継いだとして、そこにあるマニアックな式を理解・検証する事を想像すると、ちょっと勘弁してほしいといいたくなります。
何も考えずに盲目的に信用するタイプの人ならいいのでしょうが、責任ある平均的なビジネスパーソンなら、正しいかどうかわからない数式をそのまま使うのは抵抗を感じるでしょう。
結局、正しいという判断ができるまで考えることになります。
さらに将来の仕様変更の際にはまた悩むことになるでしょう。
 
もちろんすべてはバランスなので、マニアックな式を使うべき時はいくらでもあって、その場合はそうすればいいでしょう。
しかし、この場合は明らかに違っていますよね。
そのことも含めてご自分のセンスで判断をされたらいいと思います。

回答
投稿日時: 18/01/04 08:16:36
投稿者: んなっと

ISBSGRさんへ。
しっかりした内容の質問だと思います。
数式限定という前提も、前回の質問と今回の質問で統一されていて問題はありません。
気にしないでくださいね。

投稿日時: 18/01/05 14:08:38
投稿者: ISBSGR

皆さんありがとうございます。もっと詳しい使用方法を説明しておけば良かったのかも知れませんが、わずらわしくなるので、「提示数式をスマートにしたい」の要点だけで質問させていただきました。他の案件を見る場合、私は説明が長文な場合は中身を見ずにパスしてしまいます。それでも、長文をしっかり読んで回答されている方々も折られるので、感嘆してしまいます。
 んなっとさんの数式でバッチリです。作業列を使う方法は賛成なのですが、他の人が使う場合は非表示にしておく必要があるので、後から見直す場合は、分かりくくなってしまうし、。作成した本人ですら、数年もたてば俄かに構成が分からなくなってしまう恐れがあります。
 Mikeさん提案のIFERROR関数は、存在は知っていましたが、使用中のパソコンは古いので使えません。昔からこんな関数があればよいのにと思っていました。新しいバージョンで作る場合は参考にさせていただきます。エラー文字を白にする方法は、一瞬良さそうな感じがしたのですが、抽出した文字を別シートに取り出し、入力規則のリストを設定して使っているので、入力リストにはバッチリ表示されてしまいます。なので、画面では見えないのに、入力リストには表示されるのでは、かえって混乱してしまいます。
 WinArrowさんの提案のオートフィルタを使う方法は、単純な抽出だけなら問題ありませんが、抽出データを別のシートに取り出し、更に加工して使うので無理です。オートフィルタは、以前1000件ほどのデータベースで使っていましたが、操作方法を間違うとおかしな抽出になってしまいます。提案があったので、昔のデータベースを開いてテストしてみたら、やっぱしおかしい。よくよくあちらこちらを触ってみると、画面をスクロールした状態で操作すると、隠れている部分が隠れた状態で抽出されてしまうことです。スクロールバーを操作すると全部が表示されます。今はじめて問題点が分かった次第です。詰まり、標準機能を使う方法は、操作法を正確に覚えていなければならないことと、正確に操作しなければならないし、その都度操作しなければならず、バージョンが変われば操作法も変わってしまう恐れもあります。
 LMKさんのご意見はごもっともながら、上記した環境の中で良い方法をぜひともご教授戴きたい。具体的な手段が提示されないのいでは、お節介なご意見です。抽出する方法は、マクロを使えば簡単にできるのだが、マクロの知識の無い人には編集はまったく不可能だし、後から見直すのも大変。数式なら、入力セルを選択するだけでどんな数式が使われているかが一目できるし、関数のヘルプは簡単に見られるので、時間を掛けて中から順番に調べていけば分かってくるし、モーグもある。もっとも私は後期高齢者だから、気力も、思考力も、根気も衰えてきているので、昔自分で作った数式すら理解がすぐにはできない場合もある。だから、新たに数式を作るのは容易ではない。分かっていることと出来ることとの間には大きな谷間がある。
 なお、更にご意見がある方もおられるかも知れないので、もう少し開いておきます。特に、LMKさんには、素晴らしい方法をご教授いただきたく、心待ちにしています。

回答
投稿日時: 18/01/05 14:57:50
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:
 WinArrowさんの提案のオートフィルタを使う方法は、単純な抽出だけなら問題ありませんが、抽出データを別のシートに取り出し、更に加工して使うので無理です。オートフィルタは、以前1000件ほどのデータベースで使っていましたが、操作方法を間違うとおかしな抽出になってしまいます。提案があったので、昔のデータベースを開いてテストしてみたら、やっぱしおかしい。よくよくあちらこちらを触ってみると、画面をスクロールした状態で操作すると、隠れている部分が隠れた状態で抽出されてしまうことです。スクロールバーを操作すると全部が表示されます。今はじめて問題点が分かった次第です。詰まり、標準機能を使う方法は、操作法を正確に覚えていなければならないことと、正確に操作しなければならないし、その都度操作しなければならず、バージョンが変われば操作法も変わってしまう恐れもあります。

 
質問の内容だけで判断して回答しました。
オートフィルタは、あなたがおしゃっているようなおかしな現象は理解できません。
単純な抽出機能でもありません。
Excel2007以降のバージョンでは、更に機能アップして、複雑な条件抽出も可能です。
何処がどうおかしいか追及すると守はないので、この話は、これで終了。
 
操作方法を覚えないと使えない・・・それはその通りですが、
それは数式にも言えます。数式の機能や意味を理解なしで使おうとすることは、無鉄砲です。
 
オートフィルタとよく似た機能でフィルタオプションという機能があります。
これは別のセル範囲に(別シートをhk含む)にデータ抽出ができます。

投稿日時: 18/01/06 16:07:27
投稿者: ISBSGR

ありがとうございました。これで閉じます。