ウェブデータベースからダウンロードしたデータセットには、可変フォーマットがあります。目標は、すべてのデータを行にまとめ、並べ替えてCSV-KMZ(KML)ファイルに変換できるようにすることです。セルの結合を解除すると、データは次のようにフォーマットされます。これ。
私が見つけた強引な解決策は、='cell number'右側のセルに（このような。）そしてすべての空のセルを削除して、すべてのデータを同じ行に統合します（例はこちら）。ここから、セル配列を選択して下にドラッグするだけで、そのセル配列を下のセルにコピーできます。最初の画像のように、複数のサイトを持つ責任者エントリに遭遇すると、スペースが失われ、それを回避するには多くのコピーと貼り付けが必要になるため、問題が発生します。
このデータ セットは 10,000 行以上あり、このようなセットがさらに増える可能性が高いため、ここにいる優秀な人材が別の解決策を考えてくれることを期待しています。この問題について考えてくださった皆様に感謝します。
答え1
写真のデータレイアウト例が成功の鍵です。特徴がよくわかるデータ。つまり、データがあちこちに散らばるのではなく、決まったパターンに従うということです。つまり、データの各部分を収集するためのシンプルでわかりやすい数式を記述できるということです。
クリックする前にリードインを読んだとき、データがあちこちに散らばっているのかと思いました。たとえば、{City} はセル D3、E3、または F3 にあり、他のデータも同様にあちこちに散らばっているか、{State} と結合されていて、各レコードが異なっている可能性があります。結合されていないレコードもあれば、{City} が E3 に 6 つ連続してあり、F3 に 2、3 個あるなどです。
あなたはそのような欠陥にまったく悩まされません!
唯一の難点は、サイト情報が、レコードごとに 1 つのサイトだけではなく、おそらく複数のサイトから構成されていることです。しかし、これは 2 つの方法で解決できる小さな問題です。
1) レコードごとにいくつのサイトが存在できるかについての判断。結果を RP ごとに 1 行にレイアウトする予定なので、すでにその点については検討しているはずです。その点を使用してください。2) IF() 関数を使用して特定のデータをテストし、サイト情報を抽出するか、新しいレコードを開始するかを判断します。
2 番目は、1 か所だけではなく、いわば 2 か所で決定することになるため、少し違和感があるように思われます。
決定したら、出力行のセルには、決定セルの内容をテストし、行全体に波及させるより単純な数式を設定できます。
どうしますか? まず、太字の項目はわかりやすくするために追加されたもので、抽出する必要はないと想定しています。(抽出する必要がある場合は、同じ方法で実行してください。) また、1 行のデータ、一連の「空の」行、さらに別の行のデータなど、簡単な出力を計画しています。最後に、コピーして貼り付け|特殊|値を選択し、最後に大量の「空の」行を並べ替えて削除します。さらに凝った方法もありますが、午後 11:30 の時点では無理です...
主な情報である「RP」データ (T2 から始まるという考え方を使用し、「パーティー ID」が「RP#」であると想定します) を抽出するには、少しのデータを特定し、他のピースの位置をそれに関連付ける必要があります。「RP#」はそれに最適です。したがって、セル T2 には次のデータが含まれます。
= C1
ここで、OFFSET() を使用して残りの RP データをすべて検索します。ただし、出力の各行でデータを収集する必要があるかどうかを確認する必要があることに注意してください。そのため、行にデータが取得されるかどうかを確認するには、これを IF() でラップする必要があります。
= IF( C1 = "", "", C1)
これにより、T2 に RP# が入力されます。T3 から T7 では "" と表示されます。U2 に移動します。次の数式を入力します。
= IF( C1="", "", OFFSET( C1, 0, 2 ) )
C1 に RP# がある場合は、0 行下、2 列右のセルの値を検索します。RP# がない場合、U2 に "" が表示されます (サイト以外のデータを検索するすべての列で、このような結果が引き続き表示されます)。
続行します。必要に応じて 2 つの値 (行が 1 番目、列が 2 番目) を変更して、各部分を C1 に対して相対的に配置します。Thant は、要求されたすべての非サイト データを処理します。(覚えておくと興味深いこと、そして次に使用されることは、オフセットが負の値になる可能性があるため、OFFSET() を使用して、右と下だけでなく左と上も見ることができるということです。）
セルAB2には+4行のオフセットを入力します（つまり下）と-1列（つまり左)。つまり、単純な IF() テスト、次にオフセットです。データを考慮すると、各 RP に少なくとも 1 つのサイトが存在しないことは不可能に思えますが、存在しない可能性がある場合は、IF() テストに追加します。
= IF( OR( C1="", OFFSET( C1, 4, -1 ) = "" ), "", OFFSET( C1, 4, -1 ) )
右に移動してサイト データを収集し、単純な IF() テストに戻りますが、C1 の代わりに AB2 を使用します。(C1 が RP# でなかった場合は、「空の」AB2 があるため、「空の」AB2 は C1 も「空」であったことを意味し、毎回テストする必要はありません。) RP データの場合と同じように、そのサイトのすべてのデータを収集します。
ここで問題の核心です。2 番目のサイトがあるか、それとも新しいレコードの始まりか? このレコードの RP# から 7 行下にある同じ列のセルは、新しい RP# または空白です。「空白」は、以前と同様にテストできます。AK2 が 2 番目のサイトのデータの開始位置であるとします。そのセルが空白かどうかをテストします。空白の場合、2 番目のサイトがあり、上記と同じようにそのデータを検索します。次を使用します。
= IF( OR( C1="", OFFSET( C1, 7, 0 ) = "" ), "", OFFSET( C1, 7, -1 ) )
サイト番号が存在する場合はサイト番号を取得し、そうでない場合は "" を取得します。最初のサイトと同じ種類の式ですが、行オフセットを変更するだけです (列オフセットは同じになります)。サイト情報がまったく存在しない可能性がある場合は、これを変更して AB2 で "" もテストし、"空" の場合は "" の結果がここから右方向に波及するようにします。
可能だと判断したサイト データ セットの数だけこれを実行します。さらに 1 つまたは 2 つ追加してもいいかもしれません。
ここで、サイト データの存在の「もう半分」について説明します。行 3 のセル T3 に移動します。行 2 のすべてのセルを、たとえば行 15 までコピーします。C8 に達するまで、C2、c3 などの RP# がない、空っぽの行が連続して表示されます。その後、データが再び表示されます。
また、T8 は "" ではないため、その右側の行にデータが入力されます。やった!
実際のサイト コレクションの右側にあるサイト セルは、次のレコード、2 レコード、または 4 レコードまでデータを読み取るため、奇妙で紛らわしいエントリを取得する可能性があると思われるかもしれません。しかし、最初に、それらに何らかの理由があるかどうかを確認するチェックが行われ、(偽物である可能性が懸念される) サイト番号に "" が表示され、それが右方向に波及して、他のレコードのデータを読み取るのではなく、"" エントリが表示されます。心配はいりません。
持っているすべてのデータ行、または操作できる数だけコピーして貼り付けます (10,000 行のすべての数式をコンピューターに記録させるだけでなく、同時にそれらの値のコピーと貼り付けも行うことを忘れないでください)。ここでは問題がないとしますが、問題がある場合は、1,000 行のセットで管理するか、または適切に機能する可能性のあるセットで管理する必要があります。
すべての計算が完了したら、出力セルをコピーし、別の場所 (2 番目のシートなど) に貼り付け | 特殊 | 値... を実行します。 (この時点で、マシンの処理能力に問題がある場合は、最初の数行を除くすべての数式の行を削除します。)
まったく別のシートにあるため、数式抽出セルやソース データに影響を与えることなく、出力に対して何でも実行できます。
2 番目のシートに移動したら、もう 1 つだけ懸念事項があります。次に出力を並べ替えることになります。元のデータの順序が必要な場合がありますが、これは Excel が生成する並べ替え順序ではありません。その場合は、左側に列を挿入し、連続番号リストを使用して好きなように入力します。並べ替え後に数式が再計算されることによる変更がないように、数式ではなく「定数」である必要があります...
さて、すべての「空」の行を削除して、データ行のきっちりとしたセットだけにします。データを並べ替えます (必要または希望する場合は、番号列も並べ替えます)。通常どおり、低いものから高いものへと並べ替えて、「空」の行が下部に表示されるようにします。最初の「空」の行を見つけます。これを行うには、スクロールするなど、さまざまな方法があります。または、最初に任意の空行に移動し、最初の (左端の) セルに「zzzzzzzzzzzzzz」と入力して、データの末尾に並べ替え、すべての「空」の行の前の最初の行にします。最初の「空」行の最初のセルに到達したら、Ctrl-Shift-End を押して、削除する必要があるすべてのゴミをハイライト表示します。Delete キーで内容をクリアします。
これでデータ行がすべて揃い、データの使用を妨げる空ではない不要な行はなくなり、準備は完了です。作業を進めるか、コピーして他のスプレッドシートなどの最終的な保存場所に貼り付けて、作業を開始してください。
ちなみに、構築は非常に簡単で、まったく時間がかかりません。一度完了すると、データが移動しない限り、永久に完了します。ご存知のように、Web サイトが変更され、ソフトウェアがアップグレードされ、新しい列全体が変更されますが、最初の作業をゆっくりとアップグレードしていくと、これらの問題を回避する簡単な方法がたくさんあります。
一度構築すると、データはロードされた瞬間に読み込まれ、コピーと貼り付けを 1 回、並べ替えと削除 (すべてキーを押すだけ、文字通り 1 分、数時間ではありません) するだけで完了です。私がこのことを言及するのは、質問で取り上げられているように、私もあなたと同じ立場にいて、懸命に努力して問題を解決したことがあるからです。信じられないかもしれませんが、大量のエラーやマウスのスリップなど、何時間もかかる厄介で残酷な作業が、文字通り 1 分で済むようになります。もう奪われることのない人生をお楽しみください。