【速報】Microsoft MO-211試験ニュース：試験ドメイン4領域を分解→練習順序はこう組む（AI時代の勉強ポイント付き）
① 試験の全体像：配点ドメインと所要時間
MO-211試験は“実機操作”で、①ブックのオプション／設定、②データの管理と書式、③高度な数式とマクロ、④高度なグラフとテーブルを評価。試験時間は50分。日本語を含む複数言語で提供されています。直前は公式ページの「Assessed on this exam」「提供言語」を必ず再確認しましょう。
② 出題の手触り：どんな操作が出る？
実務寄りの操作が中心。例えば、XLOOKUPや動的配列系の関数を組み合わせて整表→検算、グラフは複合?二軸?箱ひげ?ウォーターフォール等の設計、マクロは記録～軽微な編集まで。“仕事でやる一連の流れ”を、試験環境で素早く再現できるかがカギです。（評価ドメインは上記①～④）
③ AI時代の勉強法：Copilot & Python in Excelの“使いどころ”
学習段階ではCopilotに「関数の意図説明」「可視化のたたき台」「データの着眼点」まで手伝わせ、理解を加速。ただし本番は“自分の手”で同じ結果を再現できる状態に仕上げること。データ分析が絡む単元はPython in Excelでpandas等を試し→同等操作をExcel機能で置き換える練習が効きます。CopilotやPythonの公式ガイドも併読を。
④ 学習リソース＆準備のコツ（日本語で固めたい人向け）
最新の出題範囲はMicrosoft資格認定 Microsoft LearnのMO-211ページと資格ページで確認→実務データで手を動かす→模擬環境で時間配分を詰める、の順が鉄板。あわせてkilltest（日語版）では、日本語版／英語版の問題集をPDF版／ソフトウェア版で用意。日本語版と英語版をオンライン同時ダウンロードして“和英つき合わせ学習”に使うのもアリです。公式情報との突き合わせで弱点（参照関数、配列思考、グラフ設計、マクロ基礎）を重点的に潰しましょう。
下記は最新試験模擬問題資料ですが、ご参考ください。
1. In E2, use `FILTER` to return rows from A2:B100 where Department="Sales" and Amount > 1000. Which formula is correct?
A. `=FILTER(A2:B100,(A2:A100="Sales")*(B2:B100>1000))`
B. `=FILTER(A2:B100,(A2:A100="Sales")+(B2:B100>1000))`
C. `=FILTER(A2:B100,(A2:A100="Sales")&(B2:B100>1000))`
D. `=FILTER(A2:B100,AND(A2:A100="Sales",B2:B100>1000))`
Answer: A
Rationale: For multiple AND conditions in dynamic arrays, multiply the Boolean arrays. `+` is OR; `AND()` doesn’t spill row-wise.
2. Return the largest value ≤ lookup value. Lookup value in F2; keys A2:A100, results B2:B100. If nothing found, return blank.
A. `=XLOOKUP(F2,A2:A100,B2:B100,"",0)`
B. `=XLOOKUP(F2,A2:A100,B2:B100,"",-1)`
C. `=XLOOKUP(F2,A2:A100,B2:B100,"",1)`
D. `=XLOOKUP(F2,A2:A100,B2:B100,,2)`
Answer: B
Rationale: `match_mode = -1` = exact or next smaller.
3. Range B3:G20 holds values, row labels in A3:A20, column headers in B2:G2. Return the intersection for row = J2, column = J3.
A. `=INDEX(B3:G20,MATCH(J2,A3:A20,0),MATCH(J3,B2:G2,0))`
B. `=INDEX(A3:G20,MATCH(J2,A3:A20,1),MATCH(J3,B2:G2,1))`
C. `=VLOOKUP(J2,A3:G20,MATCH(J3,B2:G2,0),FALSE)`
D. `=HLOOKUP(J3,B2:G20,MATCH(J2,A3:A20,0),FALSE)`
Answer: A
Rationale: Classic 2-way lookup: `INDEX` + `MATCH` + `MATCH` (exact).
4. Compute each value in B2:B101 as a share of the column total (spill the entire vector).
A. `=LET(s,SUM(B2:B101),r,B2:B101/s,r)`
B. `=LET(SUM(B2:B101),r,B2:B101/s,r)`
C. `=LET(s,SUM(B2:B101),B2:B101/s)`
D. `=LET(r,B2:B101/SUM(B2:B101))`
Answer: A
Rationale: Proper `LET` syntax with named variables and final return.
5. Build a dynamic dropdown from the unique, sorted values in D2:D100 that updates automatically.
A. Enter `=SORT(UNIQUE(D2:D100))` in E2; set Data Validation Source to `=E2#`
B. Set Data Validation Source to `=SORT(UNIQUE(D2:D100))` directly
C. Enter `=UNIQUE(SORT(D2:D100))` in E2; Source `=E2:E100`
D. Enter `=SORT(UNIQUE(D2:D100))` in E2; Source `=INDIRECT(E2#)`
Answer: A
Rationale: Use the spilled range reference `E2#` as the validation list.
6. Apply banded rows (alternate fill) to A2:F100, starting the pattern at row 2. Which CF formula?
A. `=ISEVEN(ROW())`
B. `=MOD(ROW()-ROW($A$2),2)=0`
C. `=MOD(ROW(),2)=0`
D. `=MOD(ROW()-1,2)=1`
Answer: B
Rationale: Offset the row index by the first data row to start the alternation correctly.
7. To visualize opening → increments/decrements → closing value sequence, which chart fits best?
A. Histogram
B. Box & Whisker
C. Waterfall
D. Pareto
Answer: C
Rationale: Waterfall charts highlight cumulative change across components.
8. You’re recording a macro to perform relative actions (e.g., fill two rows down from the active cell, move one column right). What should you enable?
A. Use Relative References before recording
B. Always record to Personal Macro Workbook
C. Record absolute addresses and edit code later
D. Switch to R1C1 reference style
Answer: A
Rationale: Relative recording bases steps on the active cell’s position.
9. You must prevent inserting/deleting/renaming sheets while still allowing cell edits within sheets. What should you do?
A. Protect the worksheet and tick “Protect sheet structure”
B. Protect the workbook (Structure)
C. Unlock all cells
D. Turn on Shared Workbook
Answer: B
Rationale: Workbook Structure protection controls sheet operations.
10. In a PivotTable, you want to aggregate Dates by Month. The most direct approach?
A. Add a helper column `=TEXT(Date,"mmm")` before building the pivot
B. Put Date on Rows, right-click Group, select Months
C. Put Date on Filters and filter by Month
D. Use DAX measures only
Answer: B
Rationale: PivotTables support built-in date grouping (Year/Quarter/Month/Day).
認証
お支払方法
お問い合わせ
安全なお支払い
