23 апреля 2026 г.

Excel для аналитика с нуля

Карьерник — квиз-тренажёр в Telegram с 1500+ вопросами для собесов аналитика. SQL, Python, A/B, метрики. Бесплатно.

Зачем это знать

Excel — de facto lingua franca бизнеса. Stakeholders работают в Excel. Аналитик без Excel — limited communication.

На собесах — часто basic Excel проверяется (особенно в traditional industries — banks, ritail, aerospace).

Базовые

Cell reference

  • A1: relative
  • $A$1: absolute
  • A$1: row absolute
  • $A1: column absolute

Meaning: когда copy formula, absolute не changes.

Formulas

Start с =.

=A1+B1
=SUM(A1:A10)
=AVERAGE(A1:A10)

Lookup

VLOOKUP

Legacy, common.

=VLOOKUP(lookup_value, table, col_num, FALSE)

FALSE для exact match.

Limitation: ищет только left-to-right.

XLOOKUP (new)

Better.

=XLOOKUP(lookup_value, lookup_array, return_array)

Handles left, multiple matches, defaults.

INDEX / MATCH

More flexible чем VLOOKUP.

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Works any direction.

Aggregations

=SUM(range)
=AVERAGE(range)
=COUNT(range)       - count numbers
=COUNTA(range)      - count non-empty
=COUNTIF(range, criteria)
=COUNTIFS(range1, criteria1, range2, criteria2)
=SUMIF(range, criteria, sum_range)
=SUMIFS(sum_range, range1, criteria1, ...)
=MAX(range), MIN(range)
=MEDIAN(range)
=STDEV(range)

Logical

=IF(condition, value_if_true, value_if_false)
=IFS(cond1, val1, cond2, val2, ...)  -- multiple conditions
=AND(cond1, cond2), =OR(cond1, cond2), =NOT(cond)
=IFERROR(formula, value_if_error)

Text

=CONCATENATE(A1, " ", B1)  -- или A1 & " " & B1
=LEFT(A1, 5), =RIGHT(A1, 3)
=MID(A1, 2, 5)
=LEN(A1)
=TRIM(A1)
=UPPER(A1), =LOWER(A1), =PROPER(A1)
=SUBSTITUTE(A1, "old", "new")
=FIND(text, within_text)  -- position

Dates

=TODAY(), =NOW()
=DATE(year, month, day)
=DATEDIF(start, end, "D")  -- days between
=EDATE(start, months)
=YEAR(date), =MONTH(date), =DAY(date)
=WEEKDAY(date)
=EOMONTH(date, 0)  -- last day month

Data manipulation

Remove duplicates

Data → Remove Duplicates.

Sort / Filter

Data tab → Sort или Filter.

Text to columns

Data → Text to Columns. Split string.

Pivot tables

Most powerful Excel feature.

Create

  1. Select data
  2. Insert → PivotTable
  3. Drag fields: rows, columns, values, filters

Typical

  • Rows: category / dimension
  • Values: metric (sum, count, average)
  • Slice by filters

Example

Orders data → pivot:

  • Rows: country
  • Columns: month
  • Values: SUM revenue

Matrix view instantly.

Refresh

Data changes → right-click → Refresh.

Charts

Create

Insert → chart type.

Types

  • Line: trend
  • Bar: comparison
  • Pie: composition (< 5 slices)
  • Scatter: correlation
  • Combo: dual axes

Customize

  • Axis formatting
  • Labels
  • Colors
  • Grid

Pivot chart

Based on pivot table. Refreshes автоматически.

Conditional formatting

Highlight cells по conditions.

Data → Conditional Formatting → Rule.

Examples:

  • Top 10% green
  • Below average red
  • Duplicate values yellow
  • Color scale gradient

Visual metric insight.

What-if

Goal Seek

«What input X gives output Y?»

Data → What-If Analysis → Goal Seek.

Scenario Manager

Multiple scenarios:

  • Best case
  • Worst case
  • Expected

Compare outputs.

Data Tables

Sensitivity: one input varied.

Solver

Optimization add-in.

«Maximize revenue, subject к constraints».

Data → Solver (if installed).

Linear / non-linear optimization.

Power Query

Clean и transform data.

Data → Get Data.

Similar к Python pandas, но в Excel.

Steps:

  • Import
  • Clean (trim, split, pivot)
  • Load к Excel

Repeatable (updates when data refreshes).

Power Pivot

Data model. Multiple tables, relationships.

SQL-like querying в Excel.

Большие datasets (millions rows).

Excel vs SQL / Python

Excel

  • Fast для ad-hoc
  • Stakeholder-friendly
  • Limited к ~1M rows
  • Manual refresh

SQL / Python

  • Scalable
  • Repeatable
  • Programmer-friendly
  • Requires skill

For analyst — both. Use right tool.

Общие shortcuts

  • Ctrl + C / V / X: copy / paste / cut
  • Ctrl + Z / Y: undo / redo
  • Ctrl + A: select all
  • Ctrl + Home / End: beginning / end
  • Ctrl + Arrow: edge of data
  • Ctrl + Shift + Arrow: select to edge
  • Ctrl + 1: format cell
  • Alt: menu access
  • F4: cycle absolute / relative
  • F2: edit cell
  • Tab / Enter: next cell

На собесе

«Excel proficiency?»

Scale 1-10:

  • 5: basic formulas, pivot tables
  • 7: VLOOKUP, INDEX/MATCH, conditional formatting
  • 9: Power Query, Solver, advanced formulas
  • 10: near automated data tools

Self-assess accurately.

«VLOOKUP vs INDEX/MATCH?»

INDEX/MATCH более flexible, faster на large data.

«Pivot table use case?»

Aggregate data по dimensions quickly. Example.

В Russia контексте

Excel universally used banks, retail, government, traditional industries.

Startups / tech — less (Python / SQL / BI dominant).

Advanced

Macros / VBA

Script Excel. Automate.

Legacy but powerful.

PowerBI integration

Excel → Power BI данные источник.

Office 365 co-authoring

Multiple people edit. Real-time.

New formulas

  • UNIQUE
  • FILTER
  • SORT
  • SEQUENCE

Array formulas. More powerful.

Learning path

Week 1

  • Basic formulas
  • Simple formatting

Week 2

  • VLOOKUP / INDEX-MATCH
  • Conditional formatting

Week 3

  • Pivot tables
  • Charts

Week 4

  • Advanced formulas
  • Data cleaning

Month 2-3

  • Power Query
  • Power Pivot
  • Macros (optional)

Resources

  • Microsoft Learn (free)
  • ExcelIsFun (YouTube)
  • Chandoo.org
  • SpreadsheetDev

Связанные темы

FAQ

Нужен для всех analyst ролей?

Traditional — yes. Tech — less, but useful.

Google Sheets?

Similar functionality. Online collaborative.

Обязательно Power Query?

Plus, но не must для entry-level.

Тренируйте — откройте тренажёр с 1500+ вопросами для собесов.

Тренироваться в Telegram