Video summary

2026년 컴활2급실기(엑셀) 상시기출문제 10회 문제풀이/엑셀2021/독고리 컴활2급실기

Main summary

Key takeaways

Educational

Main ideas / lessons conveyed (video overview)

  • The video walks through solving the 10th set of mock questions for the Korean “컴활 2급 실기 (엑셀)” (Excel practical exam, Level 2), focusing on Excel 2021 workflows.
  • It demonstrates how to complete tasks across multiple worksheets:
    • Basic Task 1: data entry with text repeating/copying and numeric input formatting
    • Basic Task 2: title formatting, cell styling, borders, and custom number/date/percent formats
    • Basic Task 3: conditional formatting using formulas and correct absolute references (F4 behavior)
    • Calculation worksheet: solving IF/IFF, INDEX+MATCH, COUNTIFS, HLOOKUP (with type conversion), DAVERAGE + ABS
    • Analysis Task 1 & 2: pivot tables (setup, grouping, filters, formatting) and partial sums via sorting + subtotals
    • Macro tasks: recording macros, assigning macros to form controls and shapes, testing macros
    • Chart task: customizing a 3D pie chart (data selection, linked title, slice angle, split %, data labels, texture/border)

Detailed methodology / step-by-step instructions shown

A) Basic Task Sheet 1 (data input practice)

  • Open Basic Task Sheet 1.
  • For text fields (e.g., region names like “Seoul”, “employment center”, etc.):
    • Type the first value (e.g., “Seoul”), then reuse repeating text using:
      • dropdown selection / copy-paste, or
      • drag from “fill” dots.
  • For repeated phrase fields (e.g., “Employment Center”):
    • copy the repeated string into multiple rows/columns.
  • For numeric fields (e.g., employment rates):
    • enter decimals exactly (e.g., 54.5, 48.1, 67.3)
    • continue until all required values (up to 81.3) are filled within the allotted time.

B) Basic Task Sheet 2 (formatting + custom formats + borders)

Title formatting (row 1)

  • In cell A1:
    • enter a title framed by special characters (decorative marks before/after)
  • Merge A1:H1 and center it.
  • Apply:
    • font: Gulim
    • font size: 20
    • bold
    • underline: double underline

Custom text placement / “Hangul + Hanja” input

  • Use the input method steps shown:
    • type initial Hangul
    • convert to Hanja
    • then confirm/insert via tab/selection in the two title cells area (as demonstrated).

Section styling (rows 3 and below)

  • Apply a gray accent style to A3:H3
    • Cell Style → Gray Accent Color 3
  • Merge and center:
    • A4:H7 and A8:H11 (as shown in the steps)
  • Set custom display formats for numeric/date/percent blocks:
    • C4:C11:
      • show as a “3-digit number style” by defining a custom format (e.g., write 0, then space, then 0 to achieve the intended presentation)
    • D4:D11 (date display customization):
      • month: single digit
      • day: two digits
      • day-of-week: single Korean character in parentheses
      • implemented via Custom format using a pattern like m/ d (요일) with parentheses
    • H4:H11:
      • display as percentage with exactly 1 decimal place
      • via custom number formatting (adjust “one decimal” + percent)

Header label + character/annotation insertion

  • Put label text “Classification” in A3.
  • Then add/select the character style shown from the toolbox:
    • insert a character such as “나래/나라…” (exact text imperfect in auto-captions) using the font/character tool and edit mode.
  • If inserted annotation doesn’t appear immediately:
    • re-select the cell and adjust (click again / use arrow indicators).

Borders

  • Apply borders to A3:H11:
    • border color: standard dark blue
    • thick outer border
  • Add a double border at the bottom of the A3:H3 section.

C) Basic Task Sheet 3 (Conditional Formatting with formulas)

Goal: highlight cells meeting salary/condition rules.

  • Select range A4:H12.
  • Open Conditional Formatting → Manage Rules (3 rules present).
  • For each rule, set formatting to:
    • bold
    • double solid underline
    • red text color

Rule formula approach (as demonstrated)

  • Use logical OR for “owner/representative/others” condition:
    • start with =... then incorporate OR(...)
  • Critical Excel technique:
    • use F4 twice when you want the comparison to apply to the entire row/column consistently while copying across the conditional formatting range.
  • Second condition:
    • compare base salary against the “top 2” threshold using ranking logic:
      • “rank within top 2” logic implemented via a large/threshold method
  • Ensure parentheses are correctly closed (the video emphasizes closing parentheses exactly at the end).

D) Calculation worksheet (functions)

Problem 1: Body Mass Index classification

  • Compute BMI:
    • BMI = Weight / (Height^2)
  • Create an IF-based classification:
    • BMI >= 25 → “Obesity” (비만)
    • BMI >= 20 and < 25 → “Normal”
    • else → “Underweight”
  • Implementation:
    • use IF to test first condition, then nested/next IF for remaining thresholds
    • output corresponding Korean labels.

Problem 2: Fee calculation using INDEX + MATCH (code table)

  • Use INDEX + MATCH (and “left” logic).
  • Concept:
    • take the first two letters from the payment code
    • find the matching fee row/column in the fee table
    • multiply that fee by the payment amount to get total fee
  • Steps emphasized:
    • use F4 to lock the correct lookup table range
    • extract the first two characters in the code (leftmost)
    • set the row/column indices so the fee table is referenced properly

Problem 3: Count employees eligible for salary increase

Eligibility criteria:

  • performance is >= average performance
  • promotion exam rank is within top 3
  • absence days <= 1 (one or fewer)

Function used: COUNTIFS with multiple conditions.

  • Average threshold:
    • >= AVERAGE(performance_range)
  • Promotion top 3 condition:
    • compare rank/value using the 3rd highest threshold logic (large-based)
    • use a “within top 3” derived condition inside COUNTIFS
  • Absence condition:
    • <= 1
  • Insert the result name/label in the target output cell.

Problem 4: Department breakdown using student code + lookup tables

  • Extract from student code:
    • first part: take two characters starting from the 3rd position and attach a fixed suffix (e.g., “… + 마이너스/soft/subtract” per the demo)
    • last digit: map to department table via lookup
  • Use:
    • HLOOKUP to find the department name horizontally
  • Important fix shown:
    • the lookup failed because the “3” used in lookup was treated as text
    • fix:
      • convert lookup value to a number using *1 (multiply by 1)
  • Then fill down.

Problem 5: Average difference for Grade 2 (Korean vs English) using DAVERAGE + ABS

Goal: In F28, compute the average (Korean total − English total) for Grade 2 where:

  • both Korean and English scores are >=80 and <90
  • display as a positive number

Functions used:

  • DAVERAGE for conditional database average
  • - to get difference between Korean average and English average
  • ABS to force positive output

Steps shown:

  • Build criteria ranges (database conditions):
    • Korean score: >=80, <90
    • English score: >=80, <90
  • Run DAverage for Korean within criteria (include proper field/title row)
  • Run another DAverage for English within criteria
  • Compute the difference (can be negative)
  • Wrap result with ABS (or apply ABS after calculating).

E) Pivot tables (Analysis Task 1)

Goal

  • Use sales data:
    • Sales quantity as the horizontal/granularity line
    • Region as columns
    • Sum sales amounts as the pivot value (Σ)

Steps shown

  • Select dataset and insert a PivotTable.
  • Place fields:
    • sales rate / quarter or date-related fields adjusted by dragging back month fields as needed
    • sales quantity into rows
    • region into columns area
    • sales amount into values (Σ)
  • Grouping:
    • group sales quantity values into bins 100 to 600 with interval 100
    • group by month for date display (month-only)
  • Filter:
    • exclude Seoul by unchecking its region item.
  • Pivot options:
    • set labeled cell rotation / center empty cells
    • remove total amount checkbox
  • Format:
    • set sales amount to Accounting format
    • apply pivot style (light blue, stripes, etc.)

F) Partial sums (Analysis Task 2)

Goal

After sorting product names alphabetically, compute:

  • first subtotal: sum volume fields by product
  • second subtotal: average of sales revenue and profit ranking by product

Steps shown

  • Sort product names alphabetically:
    • Data → Sort (select product name column)
  • Set up subtotals:
    • subtotal 1:
      • group by product name
      • function: Sum on volume/sales volume fields
    • subtotal 2:
      • function: Average on sales revenue and net profit/profit-ranking field
  • Critical option:
    • when adding the second subtotal, uncheck “Replace with new values” so both subtotals remain.
  • After subtotals:
    • apply number formatting:
      • thousands separator format like ###0 + unit text (e.g., “개”)
      • currency formatting for averages
  • Apply a specific table style tile (white table tile number 4, etc.) and adjust range coverage.

G) Macro tasks

Macro 1: “Bonus” calculation (record macro)

Goal: Calculate bonus in F4:F11.

  • Formula concept:
    • Bonus = Base Salary * Bonus Ratio

Steps shown

  • Developer tab → Record Macro
  • Perform the required operations for computing bonus using the stated formula steps:
    • multiply base salary by bonus ratio in the target range
  • Stop recording.
  • Create a form control:
    • insert a control for “bonus”
    • link/assign macro so clicking it computes the bonus.

Macro 2: “Clear” macro + shape button

Goal: Clear calculated content in F4:F11 using a macro.

Steps shown

  • Record macro again
  • Use Delete to clear range F4:F11
  • Stop recording.
  • Create a rounded rectangle shape:
    • select a shape and place it over cells (e.g., H6:I7 area)
    • assign the clear macro to that shape
  • Test:
    • clicking the shape clears; clicking the bonus control recalculates.

H) Chart task (Chart worksheet)

Goal: Create/customize a 3D pie chart.

Steps shown

  • Use chart data selection:
    • remove “Jo Yong-jun” data from the chart
  • Change chart type:
    • to 3D pie chart
    • select a specific 3,000-won related slice (as referenced in captions)
  • Link chart title to cell A1
  • Pie slice configuration:
    • starting angle of first slice: 30°
    • explode (split percentage): 10%
  • Data labels:
    • show item name and values
    • label position: center
  • Chart aesthetics:
    • texture fill: “recycled paper” (texture)
    • border style: rounded corners
  • Confirm and conclude chart formatting.

Speakers / sources featured

  • Teacher Dokori (host / instructor)
    • Mentions: “독고리 컴활2급실기” / “This has been Teacher Dokori” throughout the narration.

Original video