システムwiki

Office:複数のシートにわたる一貫性のないデータセットの平均

Lachlan 受付中 最終更新日:2020-08-19 19:15

こんにちは、

私はスポーツチームの統計シートを作成しようとしています.そこでは、各ラウンドに新しいシートがあり、次に平均シートがあります.

平均シートでは、名前を簡単に見つけてすぐにそのプレーヤーに関連する統計情報を取得し、プレーヤーがすべてのゲームをプレイしていない限り、これを平均化する方法を見つけることができません.

ブルートフォース方式を使用していて、Googleシートでしか機能しないため、Excelの文字制限を超えていますが、PCの速度が大幅に低下し、変更にかなりの時間がかかるため、実用的ではありません.VlookUpを試してみましたしかし、
プレーヤーはすべてのゲームをプレイしたことがあり、エラーを返し、平均的な機能を壊します.

シートは次のようになります:

名前 統計1 統計2 統計3
ジョン 5 6 3
ザック 3 8 2
スコット 4 7 4

来週は次のようになります:

名前 統計1 統計2 統計3
ジョン 5 6 3
トム 4 6 1
スコット 4 7 4

この例では、average(VLOOKUP($ A2、 'Sheet
1 '!$ A $ 2:$ D4,3、FALSE)、
VLOOKUP($ A2、 'Sheet
2 '!$ A $ 2:$ D $ 4,3、FALSE))
A2がジョンまたはスコットの場合は機能しますが、トムまたはザックの場合はエラーになります.

乾杯

返信リスト(回答:8)

1 #
AshishM

こんにちは、

Excelファイルをダウンロードできるリンクを共有します.別のワークシートに、期待どおりの正確な結果も示してください.

使用しているMS Excelのversionを教えてください.

2 #
Nothing

To:LW
re:複数のシートの平均
プレイヤーまたはラウンドの数は言いませんが...

すべてのラウンドシートのすべてのプレーヤーの名前/スコアが同じセル位置にある場合、

すべてのシートの平均式で十分です.
アクティブなプレーヤーの各ラウンドのスコアを記入します.

次に、この数式を使用します(それぞれに3つのシートと4つのセルを使用します)...

= AVERAGE(Sheet2:Sheet4!B2:E2)
すべてのプレーヤーで記入すると、空白のセルは無視されます.

'


無料のExcelワークブック「Professional_compare」は、すべてのセルまたは
2つのワークシートの各行を比較します-比較タイプの選択.
「Clean Data」および「Quick Uniques List」ユーティリティが含まれています.
ダウンロード(いいえ広告)から:https://www.mediafire.com/folder/lto3hbhyq0hcf/Documents
応答2# ->にスキップ
3 #
Lachlan

シートはフィルターで並べ替えられているため、コーチはさまざまな統計のリストを並べ替えることができます.

ゲームごとに20人のプレーヤー、16ラウンド、プレーヤーごとに24の異なる統計があります.シートのversionをすぐに入手できるようにします.

応答3# ->にスキップ
4 #
Rohn007

「フル」データセットを必要としません.1ゲームあたり4または5人のプレーヤー、プレーヤーごとに2つか3つの統計、2または3ラウンドは適切であろう.

実際には、統計の束を作成し、統計を生成するために式に頼るのではなく、代替アプローチを検討してください.

すべての情報を含む単一の「マスター」データ入力tableを作成します.次に、フィルタやスライサーとピボットtableを使用して結果を生成します.あなたはデータにゲーム(そして丸い)情報を追加する必要があります.例えば:

最初のステップ、すべてのデータを単一のタブにコピーし、余分な見出しを削除

注1:John Game 1 Stat 1の値を最終ピボットtable

でCalcの結果を表示するように変更しました

注2:+ショートカット

を使用して、データをExceltableとして定義しました.

注3:SELECTサブセットのデータを表示するための「スライサー」を追加しました

注4:データ入力を容易にするために複数の統計フォーマットで1行を残しました

次にPowerQueryを使用して、ソースデータを1行あたり1つのstatに "Unpivot"

上記の「無ピボット」データtableを生成しましたが、最終versionのためにそれをする必要はありません.

は、非ピボットデータを使用して、バックグラウンドでの式を生成するピボットtableを生成します.結果として生じるピボットtableは最小限のフォーマットでこのように見えます:

注:「John」STAT1の平均は、1つの値を「1」に変更しました(平均関数が本当に取り組んだことがわかりやすくするために).

この例では、セットアップに5分以内に連れて行った.

特定のサブセットへのフィルタリングを可能にするためにピボットにスライサーを追加することもできます.

だから、スライサーを使ったピボットを見て、もう1つのデータ列、チーム名を追加します.そのようにして、あなた、またはコーチは彼らのチーム統計だけを見るためにチーム名を選択することができます.

これは私のQuickieの例へのリンクです:

あなたがする必要がある次のことはトーナメント「ランキング」を生成することです.

Microsoft Excelの電源クエリの使用-PowerQueryを使用して合計とランクデータtableを実行します. https://www.youtube.com/watch?v=ki5zeoqhb9e&feature=youtu.be.8min
電力query.mp4(ダウンロード)
Microsoft Excelでの電源クエリを使用して自動合計とランクを作成する方法を説明します.Szilviaデータをセットアップし、それを電源クエリにプルし、最初の行をヘッダーに宣伝し、結果を宣伝してからそれらを組み合わせることを実行します.
by.
0:00intro
1:09start-データソース、 "Scorecard" "1 :20名前付き範囲を作成するためのデータを選択する
1:44パワークエリデータタブ>データコマンドの取得>他のソースオプション>ブランククエリ>1 :58オープンPowerQuery Editorは、何かに照会する
2:30pq excel.currentworkbook()にデータをロードすると、ワークシートのデータのスキーマ、この場合は名前付き範囲 "Scorecard"
3:05セットフィルタフィルタ>テキスト範囲>を使用して名前付き範囲内でない新しいデータを維持する>equals>drap to select値 "scorecard"3:30「コンテンツ」列を右クリックしてください>「他の列を削除」を選択します.3:40"Content"を展開する範囲内のすべてのデータを表示します.4:00生成された列名を最初の行の列名で置き換えます.home tab>transform group>「ヘッダとして1行目を使用」ドロップダウン
4: 20Unpivot:[チーム](名前)列[変換]タブ>[任意の列]>[列]列コマンド>[列]>[列]4:55ほとんどの準備ができていません.)「GroupBy:Home Tab>Transform Group>Group byコマンド>グループ別:Team列、新しい列名:実行中の合計、列、列:値
5:50現在のスコア合計の折りたたみ表、「Running Total」6:00ランニングの前に、実行中の合計を並べ替える必要があります.列見出しのドロップダウンをクリックして、6:22追加索引列を使用したランク列を追加:列タブの追加>一般的なグループ>索引列ドロップダウン>1
6:50rename index colmn:lank
6:58ワークシートに戻してロードバックします.ホームタブ>閉じる>現在のワークシート上のtableへ
7:30更新:データの値の変更範囲、PQ生成ランクtableを右クリックし、「更新」を選択します.

ランクがLAに最小Excelのピボットtableでは https://www.myexcelonline.com/blog/rank-smallest-largest-excel-pivot-tables/

Excel Pivot Tablesには、以下の有用な計算がたくさんあります. 値をオプションとして表示し、あなたがたくさん役立つことができるものは ランクが最も小さい計算には、
このオプションはあなたの値のランキング(最小値が最小の値である)を計算し、リスクや機会をすばやく特定することができます.
ET PTピボットtable電源クエリBI.DOCX

ピボットtableとランクランタイム:3:15 https://exceljet.net/lessons/how-to-rank-with-a-pivot-table.

ピボットtableは、数式なしでデータを素早く分析するのが非常に簡単になります.この例では、Pivot Tableを使用して製品の販売を商品別に要約して、リスト内の各製品にランクを割り当てます.これはあなたが1分以内にできることです.

応答4# ->にスキップ
5 #
Lachlan

私はピボットtableを作成し、それが私に取得する場所を見ることに行くでしょう.

私はこのGoogle Docで私がやろうとしていることの例を持っています.必要に応じて編集できます.スプレッドシートをかなり遅くすると、私の元のソリューションを削除しました.

リンク:編集

応答5# ->にスキップ
6 #
Rohn007

ピボットtableに精通していない場合、そして関連する特徴は、無料のウェビナーのいくつか(すべて?)を見るために1時間か2を投資するのに価値があると思います.彼らはピボット材の可能性の非常に良いデモンストレーションであり、また役に立つことができます
学習ツール(ビデオファイルをダウンロードした場合)

サンプルファイルのコピーをダウンロードしてビットで再生するつもりです.

ピボットtableについての具体的な質問をしてください.私はまだ自分自身を学んでいますが、答えようとします.それとも他の誰かが提案と一緒にジャンプします.

これは私が言及したリンクです.

ピボットtableコース-Wiki.

次のWikiは、さまざまな「高度な」Excelツールのリンクの集まりを持っています.無料のウェビナー、短い記事、そしていくつかの無料で有料の電子ブックへのリンク.

私は将来の参照のためにWebInarの再生録音をダウンロードし、例のワークブックをダウンロードします.
あなたがgung hoであるならば、あなたは将来の相互参照を作るために録音されたセッションに戻る時間参照を追加して、将来の相互参照を簡単に見つけることができました(私はそれをしています).

これらの無料セッションはそれぞれおよそ1時間続いてそれぞれ、彼らの関連コースのための短いシル(それは間違いなく価値のある価値がある).

この次の記事リンクはPowerBiに関する興味深い視点ですが、感情はExcelに組み込まれているのと同じ「電源ツール」にも適用されます.

@ 彼らが話すように構築2018 08 16.
Matthew Runyon
ラインレベルまたは下/中間管理のレポートをどのように構築しますか?手を見せる?あなたのたくさん?うん、それが私が考えたものです.そしてそれらのうちの何人の世話をしているかどうか?
複雑な分析?
クールなナビゲーションボタン?
実際には、それらのうち何が気にかけていますか.それを超えて、「私には本当にすばやく、具体的な質問に対する答えを見せることができますか?そしてそれを輸出してそれに取り組む必要があるとき、それは私にその背後にあるすべてのデータを与えることができますか?私が走っている問題、通常はそうではない
そのことをする必要があるのかわかりません.それは彼らがそれを最初の場所で作りたいことについて十分な詳細を得ることができないことです.しかし...これが電源BIについてのことです.ほとんどの基本的な分析をするのは早いです.それは十分速いです、
実際には、あなたは彼らの目の前に分析を正しく構築することができます.

これはクイックイントロです:.

!なぜピボットtable(Wiki)
https://youtu.be/3ko_yqupbce.(10分)
人々がピボットtableを始めるだけではたくさんの質問があります.ピボットtableがクールな方法を説明しようとすることができます.データを分析する方法、さまざまな方法で要約しているのは、どのようにして様々な方法でも本当に優れている方法、
Blah、Blah、Blah...
ティボットtableの価値がある理由を見るための最良の方法の1つは、ピボットtableレポートの構築を手動で同じレポートを構築するプロセスに比較することです.それはまさに今日のビデオで何をするのかです.
ピボットtableは本当に使いやすいですか?ビデオを見て、自分のために見えます!

「PowerQuery」/「Get&Transform」>table>ピボットtable>PivotCharts>Dashboards...PowerBi
記事:
Get&Transform>Tables>Pivottables>PivotCharts>Dashboards...

e wiki-get&変換-データモデリング-table-ピボットtable-Pivotcharts-ダッシュボード-Powerbi.docx.

Office 2007以降、MSは多くのオートメーションを追加するいくつかの「電源」ツールを導入しました.残念ながら、これらのツールはめったに話をしていないので、効果的に「隠されています」.あなたがこれらの機能と彼らの自動化のいくつかのデモを見ることに興味があるならば、
次の記事には、表示できるいくつかの無料のWebInarのリプレイへのリンクがあります.私はこれらの新しい電力機能について学ぶ出発点としてそれらを使っていました.
その中の初心者レベルのプログラマーとしても、私はこれらのツールにぴったり感銘を受けていて、それらを使って新しい「トリック」を学ぶことを試みています.これらのツールは非常に柔軟です.
PowerQuery(PowerPivot)>table>ピボットtable>Pivotcharts>Dashboards...またはPowerBi.
Wikiの最後には無料へのリンクがありますこれらの機能についてPDF電子ブックをダウンロードしてください.

ここは、さまざまな「電源」ツールの簡単な説明です.

ヒント:これらの "Power"ツールでは、MSは通常のゲーム、名前を変更しています.
たとえば、1つのツールはPowerQueryと呼ばれ、2016年に「Get&Transform」と呼ばれている場合は、2019年のPowerQueryに戻りました.
「PowerPivot」と「データモデリング」と非常に曖昧な表情がありますが、MSはそれらを定義していません.

PowerQuery(Get&Transform)は、Excelの内外で80以上の異なるデータソースからデータをインポートするために使用されます.その後、データを「クリーニング」しフォーマットすることができます.PowerQueryの後で新しいデータをインポートできるように変更を記録します.
自動的に同じ操作を適用します

PowerPivot/データモデリング
Power Pivotはすべて、おなじみのピボットtableツールを使用してデータの分析に関するすべてのExcelユーザーが知っておくべきことが
PowerPivotはデータモデリングテクノロジです.これにより、さまざまな入力table間の関係を作成し、計算列を作成するための「Geek Speak」の「データモデル」を作成できます.Power Pivotを使用すると、非常に大きなデータセットで動作することができます.
関係、および複雑な(または単純な)計算、すべて高性能な環境で、そしてExcelの身近な経験の中ですべて.一言で言えば、複数のデータソースを使用することができます.それからあなたはインポート、マージし、そして分析を実行することができます 結果のデータについて
.Excelは「ネイティブに」100万行のデータのみを保存することができます.あなたが100万行の制限を打つ前に、特にあなたがたくさんのvlookupsなどを持っているならば、Excelは非常に貧弱な実行を始めます
Power Pivotはソースデータを圧縮し、それをExcelワークブック内のデータベースとして保存します.つまり、10桁の行のデータを、Excelワークブックに直接圧縮して実行する方法で保存できることを意味します.あなたは配布することができます
元のソースデータにアクセスする必要なしの他の人々に結果を得ます.
.

table
tableは、データをグリッド形式で行い、単純な行/列の書式設定を適用します.tableは、単純な行列(A1)参照を好み、「構造化参照」を使用します.構造化された参照では、列名を使用して、Fomulasを解釈しやすくします.
表に数式を入力すると、tableに追加されると、table内のすべての行に自動的にコピーされます.PowerQueryデータをtable形式にエクスポート/保存します.ピボットtableは入力データをtable形式にします.
.

ピボットtable"ドラッグアンドドロップして"ユーザーインターフェイスを使用して複雑な数式を自動的に生成します.計算と書式設定オプションは単に素晴らしいです.ウェビナーでは、「ピボットtableの長所」は迅速に例を提供することができます
それらを説明できるように.練習では、ユーザーと話している間に「Quickie」の例を作成できるはずです.ピボットtableは「スライサー」に接続することができ、ユーザーにピボットtable(およびLinked PivotCharts)をフィルタリングする(およびLinked PivotCharts)
.

pivotchartsは、ポワポリブル生成データの動的グラフィカル解釈を提供することができます.

ダッシュボードは、スライサーまたはフィルタを介してユーザーに、単一のコンパクトディスプレイ内の多くのデータのダイナミックコントロール、すなわちフィルタを提供するピボットtableおよび/またはピボットチャートの「単なる」コレクションです.

PowerBiは、すべての "Powertools"を組み込んだ新しい(無料)の標準andaloneApplicationです.実際には、Excelに内蔵されているツールよりも多くの機能、およびより多くのチャートのオプションがあります.MSは非常に積極的にこのツールの開発に取り組んでいます.
月額の更新で新機能を追加します.

電源BIエンドツーエンド図の更新版
2019 11 19
https://www.coatesDatastraze.com/blog/version-of-the-power-bi-end-to-diagagram.www.coatesDataStrazes.com...
電源BIエンドツーエンド図の更新版が利用可能になりました.最新versionが常に
rohn007:PowerBi環境の本当にクールな高水準ビューです..

!パワーピボットとは何ですか? https://powerpivotpro.com/what-is-power-pivot/
Power Pivotは、データとの関係の「欠けているリンク」として最適です.(開始する準備ができている場合は、今日の電源ピボット2016を取得できます.以下のオフィスversion:
365 Pro Plus、E3、E5、またはリテールプロ).
最後の5年間の経験では、他のテクノロジはそのゲーム変化の能力に近づいていません.彼らの潜在的な可能性を実現し、それらの3つの資質を完全に活用するために 上記
あなたの背景によっては、どのような電源ピボット、その役割、そしてそれがあなたのためにできることを理解するためのいくつかの「最善」の方法があります.
Power Pivotはこれらすべてのことです、以下のセクションで簡単に説明します.
* 20年間でExcelの最高の新機能
マイクロソフトのパワービブスイートの脳(そして心!)
*ビジネスインテリジェンスへのアジャイル&費用対効果の高いアプローチ

! Power Pivot:Excelの強力なデータ分析とデータモデリング
support.office.com/ja-jp/article...
Power PivotはExcelアドインで、強力なデータ分析を実行し、洗練されたデータモデルを作成するために使用できます.Power Pivotを使えば、さまざまなソースから大量のデータをマッシュアップしたり、情報分析を迅速に実行したり、洞察を簡単に共有できます.
ExcelとPower Pivotの両方で、データモデル、関係のあるtableのコレクションを作成できます..Excelのワークブックで表示されているデータモデルは、Power Pivotwindows に表示されるのと同じデータモデルです.Excelにインポートするデータはすべて入手可能です
パワーピボット、およびその逆..

!なぜピボットラブル
https://youtu.be/3ko_yqupbce.(10分)
人々がピボットtableを始めるだけではたくさんの質問があります.ピボットtableがクールな方法を説明しようとすることができます.データを分析する方法、さまざまな方法で要約しているのは、どのようにして様々な方法でも本当に優れている方法、
Blah、Blah、Blah...
ティボットtableの価値がある理由を見るための最良の方法の1つは、ピボットtableレポートの構築を手動で同じレポートを構築するプロセスに比較することです.それはまさに今日のビデオで何をするのかです.
ピボットtableは本当に使いやすいですか?ビデオを見て、自分のために見えます!

応答6# ->にスキップ
7 #
Rohn007

なぜ各ラウンドで2つの別々の繰り返しセットの名前Q1、Q2、Q3、Q4?

Q名は何を示していますか?修飾子(決勝)?

あなたのサンプルファイルの4 and Upは空白になってから、混乱を排除し、例ファイルを簡素化するために削除して削除してください.

応答7# ->にスキップ
8 #
Rohn007

これはあなたの例のワークブックに基づく高速で汚れたピボットです.主に、計算された数字が正しいことを確認しています.

.

注:

3丸みのタブからデータをコピーして新しいタブに貼り付けて、「マージされた入力」タブを作成しました.その後、空白の行と合計と計算行を削除します.それらは後で再現することができます

注:

名前はアルファベット順に分類されているので、奇妙なソート順

注:

ピボットでは、「列ラベル」はアルファベット順に表示されます.「カスタム」ソート順を作成し、それを使用して(試みなかった)ことでそれを修正できるはずです.

注:

マイ例はXLSB形式であることが明らかにXLSX形式では長すぎるためです.

私が3つの丸いタブを削除した場合、私はおそらくこのエラーを取り除くことができました.

注:

あなたの計算された列の、すなわち処分効率I ifError()関数を使用して#div.0を置き換えます! 0値

のエラー

注:

1つのワークブックに複数の年を含める場合は、その値のスライサー/フィルタを許可するために1年の列を追加する必要があります.

<編集>

おっと、私は%列のフォーマットを調整するのを忘れました.やるのに十分簡単です.

© システムwiki All Rights Reserved.