ナナエフ

同人ゲームサークル『7-FIELD』制作ブログ  -ゲーム制作やイベント参加などの活動情報 + 雑記-

スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。
  1. --/--/--(--) --:--:--|
  2. スポンサー広告

エクセルの文字列データを関数で並び替えるには

tetsuwoです。 【 twitter

「エクセルに入力したデータ、うまく並び替えられない?」

頼まれたのは文字列データのソートでした。しかも関数で。


IF関数、COUNTA関数、ROW関数、INDEX関数、MATCH関数、COUNTIF関数

を組み合わせることで解決したので、需要があるかと思いサンプルとして紹介。


▼ 関東の都県一覧。神奈川は「シン」読みで埼玉(サイタマ)の下に。



・・・と言っても、某サイトからのコピペなので理解が追いついておらずモヤモヤが。

ちゃんと調べてしっかり理解しておこう。



厳しい条件

初めに書いたエクセルのデータ並び替え依頼。

(PC操作ほぼ無縁の)職場で受けたこの質問、一見簡単に解決できそう?と思いきや

付け加えられた条件で一気に辛いものに。


<追加条件>

  ・入力データは不定期に増減する
  
  ・入力者は文字入力以外の操作をしない  ⇒ 並び替え機能縛り
  
  ・入力データは文字列  ⇒ 単純な数値の大小比較では解決しない
  
  ・エクセルのセキュリティ設定を変えない  ⇒ VBAソート縛り


サッパリ妖精。全然知識・技術が追い付いてない!


あっさり解決

ネットは凄い。

同じようなことで悩む人が沢山いるし、その数だけ色々なアドバイスも見られるので

サラリと欲していた結果を出すことが出来る。


▼ とりあえずの正解は赤枠内の関数【 =IF(COUNTA(A:$A)<ROW(),"",INDEX(A:A,MATCH(ROW(),INDEX(COUNTIF(A:$A,"<="&A:$A),),)))



画像ではセル (C, 1) を選択した状態ですが、セル (C1:C20) くらいまで同じ関数を入力。

見事に入力した都道府県が50音順になってる!



とりあえず結果が出ればいいや、の方はここで終了。

何となく関数を分解してみた結果は次項で。解決はしない。


関数を分解してみる

▼ 上記と同じ画像ですが、ここで注目するのはE列



■1, 2行目

C列に入力されている関数。

一番大枠で使用してるのは IF関数 なので、その引数を 条件部真判定偽判定 で色分けした。

真判定は表示なしのダブルクォーテーションだけで意味なし。



■3行目以降

IF関数の内容がややこしいため、細分化して処理結果を確認した。

G列に検証する関数・引数を記載し、H列はG列の先頭にイコールをつけて関数として有効化したもの。


結果は次行以降の通り。



関数検証№①  IF条件部 / COUNTA(A:$A)
  
  いきなり見たことない引数の指定方法。
  
  しかし内容は単純で、A列全体からデータ入力があるセル数を返すのみ。
  
  1行目~11行目まで書いているので、結果は「11」。
  
  
  ROW関数は行数を返すだけなので、条件部分でやってることは
  
  A列の総データ入力数より、「関数が入力されている行番号」が大きいか? と言うことの判定。
  
  
  
  セル【C, 12】にもこの関数が書かれている。
  
  A列の総入力数は11のため IFの条件が「11 < 12」となり、「真」として空白が返っている。
  
  IF条件部の検証はこれでOK。




関数検証№②, ③,③', ③''  IF判定-偽部 / COUNTIF(A:$A,"<="&A:$A)
  
  偽部の中で一番大枠となっているのは INDEX ですが、引数が相当混乱を招く入れ子となっているため
  
  最下部から順に理解しながら上っていく計画。
  
  
  
  ③の COUNTIF(A:$A,"<="&A:$A) の引数指定がまた不思議。不等号って文字列入力として受け付けるのか。
  
  まずは A:$A だけで何が返るのか③の前に②で確認、としたところセル【A列, 関数入力行】が返る。画像ではセル【A, 5】で千葉。
  
  
  
  A:$A の意味が分かったところで、素直に③を実行してみると結果は「9」。
  
  セル【A, 関数入力行】の値(画像では「東京」)が、A列をソートしたら何番目のデータになるかが返っている。
  
  COUNTIF の第1第2引数は、共通で A:$A を使用していても、№①のようにA列全体を意味する使われ方と、№②のようにソート対象データが入る使われ方をしている。
  
  
  
  では③で分かった事を直接代入して確かめてみよう!としたのが③', ③''。
  
  ここでギブアップ!!!
  
  ③'では、A列7行目データなのだからと、引数に「神奈川」を入れたらエラー。ダブルクォートで囲んでもエラーは消えず。
  
  
  あぁそれはそうか、COUNTIF は第1引数が範囲だったと試したのが③''。
  
  第2引数に固定値を入れると、エラーにはならなくとも「0」。北海道は「11」のはずなのに。




関数検証№④, ⑤, ⑥  IF判定-偽部 / INDEX, MATCH
  
  ③が理解できなければ、あとはもう引数の値を調整することも出来ず
  
  ④, ⑤, ⑥ と連続で納得できない値やエラーを確認したのみ。
  
  そもそも一番外で使っている INDEX は、少し調べたら必須引数が3つあるはずなんですけど・・・。




そんな訳でなぜ動くのかあまり理解できないまま使うと言う、気分が悪くモヤっとした状態なので

近いうちにどうにか理解しておきたいと思う次第。




それとか、実はもっと簡単なソート方法があったりして?

どなたかご存知でしたら教えてくだされ!


宣伝

舞台は企業都市-エリアシティー-。特殊な権限を巡って争われる

イミテーションゲーム<ニセモノ探し>を参加者6人の視点から描いた群像劇型サウンドノベル。


jacket_05_20150318b.jpg

当サークル第3弾作品「露草ユーフォリア」、DL・通信・委託販売中です。

ダウンロード販売・・・DLsiteさま、メロンブックスDLさま

通信販売・・・とらのあなさま、メロンブックスさま、三月兎さま

体験版も公開しておりますので、目に止まりましたらお試しあれ。


第2弾「薄鈍アドベント」もよろしくお願いします。
 
スポンサーサイト
  1. 2015/09/07(月) 07:00:00|
  2. 雑記
  3. | トラックバック:0

トラックバック

トラックバック URL
http://7fblog.blog.fc2.com/tb.php/558-95809be7
この記事にトラックバックする(FC2ブログユーザー)

カウンター

プロフィール

akinasi, tetsuwo

Author:akinasi, tetsuwo
FC2ブログへようこそ!

検索フォーム

カレンダー

07 | 2017/08 | 09
- - 1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31 - -

最新記事

最新コメント

最新トラックバック

月別アーカイブ

カテゴリ

告知 (63)
制作 (88)
ソフト紹介 (19)
素材紹介 (10)
言外 (7)
雑記 (361)
ナナエ (29)

リンク

このブログをリンクに追加する

上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。