Google Sheets для аналитика

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

Google Sheets — standard для tech-startups, many Russian companies. Collaborative, cloud-first. На собесах в modern compaines — ожидается.

Sheets vs Excel

	Sheets	Excel
Platform	Web / cloud	Desktop primarily
Collaboration	Excellent	Good (365)
Offline	Partial	Full
Cost	Free	Paid
Scale	< 10M cells	~1M rows
Power tools	Limited	Power Query, Pivot
APIs	Google, scripts	VBA, add-ins

Pick based:

• Startup / collaborative → Sheets
• Enterprise / heavy data → Excel

Basic formulas

Same как Excel:

=SUM(A:A)
=VLOOKUP(..)
=IF(...)
=COUNTIF(...)

Mostly compatible.

Unique features

ArrayFormula

Apply formula к entire range:

=ARRAYFORMULA(A2:A * B2:B)

Instead of dragging.

QUERY

SQL в Sheets:

=QUERY(A:C, "SELECT A, SUM(C) WHERE B > 100 GROUP BY A", 1)

Powerful. Makes Sheets SQL-like.

IMPORTRANGE

Import другого sheet:

=IMPORTRANGE("URL", "Sheet1!A:C")

Cross-sheet data flow.

FILTER

Dynamic filtered range:

=FILTER(A:C, B:B > 100)

UNIQUE / SORT / SEQUENCE

Simple array generators.

Functions specific

IMPORT functions

=IMPORTHTML("url", "table", N)
=IMPORTXML(..)
=IMPORTDATA(..)
=IMPORTFEED(..)

Scrape web data.

GOOGLEFINANCE

=GOOGLEFINANCE("AAPL", "price")

Real-time stock data.

GOOGLETRANSLATE

=GOOGLETRANSLATE("hello", "en", "ru")

Translation.

Collaboration

Real-time

Multiple users edit simultaneously. Cursors visible.

Comments

Tag colleagues, threads.

Version history

File → Version history.

Suggesting mode

Instead edits. Track changes.

Apps Script

JavaScript для automation:

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  // Custom logic
}

Triggers:

• Time-based (every hour)
• On edit
• On form submit

Integrations

Google Forms

Forms → Sheets automatic. Survey analytics.

Google Data Studio (Looker Studio)

Sheet → dashboard.

BigQuery connector

Query BigQuery directly from Sheets (limited).

Connected Sheets (enterprise)

Live BigQuery integration.

Для analyst workflow

Ad-hoc

Quick pivot, share к stakeholders.

Metrics tracking

Simple spreadsheet обновлённое API.

Surveys analysis

Form results → Sheet → pivot.

Small datasets

Under million cells. Fast.

Limitations

Scale

~10M cells max. Excel ~1B.

Performance

Slow на huge sheets с many formulas.

Formulas

Fewer than Excel. But basics cover 95% needs.

Power tools

No Power Query, Power Pivot.

Pivot tables

Data → Pivot table.

Similar Excel. Slightly different UI.

Charts

Insert → Chart.

All common types. Less customization vs Excel.

Conditional formatting

Format → Conditional formatting.

Rules, color scales, custom formulas.

Data validation

Data → Data validation.

Dropdowns, formats, ranges.

Shortcuts

• Cmd/Ctrl + /: show shortcuts
• Cmd/Ctrl + Shift + V: paste values only
• Alt + Enter: new line в cell
• Cmd/Ctrl + Arrow: edge data
• Cmd/Ctrl + Shift + Arrow: select к edge
• Cmd/Ctrl + K: insert link

Pro tips

Named ranges

Data → Named ranges. Formula readability.

Protection

Protect sheet / range. Prevent edits.

Conditional formatting formulas

Custom rules с formulas. Dynamic.

Shared templates

Company analysis templates. Copy reuse.

Common workflows

1. Data pull

=IMPORTRANGE(...)
or
Apps Script fetching API

Data arrives.

2. Transform

QUERY, FILTER, ARRAYFORMULA.

3. Aggregate

Pivot table.

4. Visualize

Chart.

5. Share

Link / add viewers.

All in one sheet. Pipeline-like.

Integration с SQL

Query warehouse → export CSV → upload к Sheets.

Или Apps Script connecting к database (BigQuery integration).

Для собеса

«Google Sheets experience?»

• QUERY function
• Collaborative features
• Apps Script (если знаете)
• Pivot tables

«Excel vs Sheets?»

Sheets — collaboration, cloud. Excel — power, scale.

Russian context

Popular в modern startups.

Less в government, banking (Microsoft stack).

Limitations для analyst

• Big data — no
• Complex transformations — Python better
• Strong ML — Python

For many daily tasks — sufficient.

Google Workspace

Part of Google Workspace (ex-GSuite). Integrated с Docs, Slides, Drive.

Enterprise plans features:

• Connected Sheets (BigQuery)
• More storage
• Admin controls

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

• Excel для аналитика
• SQL vs Excel
• Looker

FAQ

Free?

Yes. Free personal. Paid в Workspace.

Offline?

Chrome extension allows. But main — online.

Работает в Russia?

Yes (as of now). Check latest.

---

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