Karatasi ya Muhtasari ya Fomula za Google Sheets

Bila Malipo

Rejeo la Google Sheets linalotafutika na kuchapishika: QUERY, ARRAYFORMULA, IMPORTRANGE, FILTER, lookups, tarehe na njia za mkato za kibodi. Bure.

QUERY function

=QUERY(A1:D, "SELECT A, B")

Pick columns like SQL

=QUERY(A1:D, "SELECT * WHERE C > 100")

Filter rows by number

=QUERY(A1:D, "SELECT A WHERE B = 'Paid'")

Filter by text (single quotes)

=QUERY(A1:D, "SELECT A WHERE B CONTAINS 'inc'")

Substring match

=QUERY(A1:D, "SELECT A, SUM(C) GROUP BY A")

Aggregate per group

=QUERY(A1:D, "SELECT A, COUNT(B) GROUP BY A PIVOT D")

Pivot a column into headers

=QUERY(A1:D, "SELECT * ORDER BY C DESC LIMIT 10")

Top 10 rows

=QUERY(A1:D, "SELECT * WHERE A IS NOT NULL")

Drop blank rows

=QUERY(A1:D, "SELECT A WHERE D >= date '2026-01-01'")

Date comparison syntax

=QUERY(A1:D, "SELECT A LABEL A 'Name'")

Rename output header

=QUERY({Sheet1!A:C; Sheet2!A:C}, "SELECT *")

Query stacked ranges

=QUERY(A1:D, "SELECT Col1, Col3", 1)

ColN names for {} ranges

ARRAYFORMULA & arrays

=ARRAYFORMULA(A2:A * B2:B)

Apply a formula to a whole range

=ARRAYFORMULA(IF(A2:A="", "", A2:A * 1.2))

Skip blank rows in arrays

={A1:A5; B1:B5}

Stack ranges vertically

={A1:A5, B1:B5}

Place ranges side by side

=SEQUENCE(10)

Numbers 1..10 down a column

=SEQUENCE(3, 4, 0, 5)

3x4 grid from 0 in steps of 5

=FLATTEN(A1:C5)

Collapse a range to one column

=TRANSPOSE(A1:A10)

Flip rows and columns

=BYROW(A2:C10, LAMBDA(r, SUM(r)))

Run a lambda on each row

=MAP(A2:A, LAMBDA(x, x * 2))

Transform each cell

=SCAN(0, A2:A, LAMBDA(acc, x, acc + x))

Running total via lambda

IMPORT functions

=IMPORTRANGE("sheet_url", "Data!A1:C100")

Pull a range from another file

IMPORTRANGE: click "Allow access" once

First use needs authorization

=QUERY(IMPORTRANGE(url, "A:C"), "SELECT Col1")

Filter imported data

=IMPORTXML(url, "//h2")

Scrape a page via XPath

=IMPORTXML(url, "//a/@href")

Extract link URLs

=IMPORTHTML(url, "table", 1)

First HTML table on a page

=IMPORTHTML(url, "list", 2)

Second HTML list on a page

=IMPORTDATA("https://x.com/data.csv")

Load a CSV/TSV by URL

=IMPORTFEED(rss_url, "items title", TRUE, 5)

Latest RSS feed entries

Imports refresh ~every hour, not live

Caching caveat to remember

FILTER / SORT / UNIQUE

=FILTER(A2:C, C2:C > 100)

Rows where condition is true

=FILTER(A2:C, (B2:B="Paid") * (C2:C>0))

AND conditions (multiply)

=FILTER(A2:C, (B2:B="A") + (B2:B="B"))

OR conditions (add)

=SORT(A2:C, 3, FALSE)

Sort by column 3 descending

=SORT(A2:C, 1, TRUE, 2, FALSE)

Multi-column sort

=SORTN(A2:C, 5, 0, 3, FALSE)

Top 5 rows by column 3

=UNIQUE(A2:A)

De-duplicate values

=UNIQUE(A2:C)

De-duplicate whole rows

=SORT(UNIQUE(FILTER(A2:A, A2:A<>"")))

Clean sorted distinct list

=COUNTA(UNIQUE(A2:A))

Count distinct values

=RANDARRAY(5, 1)

5 random numbers 0..1

Lookups

=VLOOKUP(E2, A2:C100, 3, FALSE)

Exact match, return column 3

=VLOOKUP(E2, A:C, 2, TRUE)

Approximate (sorted) match

=XLOOKUP(E2, A2:A, C2:C)

Modern lookup, any direction

=XLOOKUP(E2, A2:A, C2:C, "Not found")

Built-in default value

=XLOOKUP(E2, A2:A, C2:C, , , -1)

Search from the bottom

=INDEX(C2:C, MATCH(E2, A2:A, 0))

Classic INDEX+MATCH combo

=INDEX(A2:F, MATCH(x, A2:A, 0), MATCH(y, A1:F1, 0))

Two-way lookup

=HLOOKUP(E1, A1:F3, 2, FALSE)

Lookup across a row

=ARRAYFORMULA(VLOOKUP(E2:E, A:C, 3, FALSE))

Bulk lookup whole column

=IFERROR(VLOOKUP(...), "-")

Hide #N/A when not found

Text functions

=SPLIT(A2, ",")

Split text into columns

=JOIN(", ", A2:A10)

Join values with a separator

=TEXTJOIN(", ", TRUE, A2:A10)

Join and skip blanks

=REGEXEXTRACT(A2, "\d+")

Extract first number

=REGEXEXTRACT(A2, "@(.+)$")

Capture group (email domain)

=REGEXREPLACE(A2, "\s+", " ")

Collapse repeated spaces

=REGEXMATCH(A2, "^https?://")

TRUE if pattern matches

=LEFT(A2, 5) / =RIGHT(A2, 3)

First / last characters

=MID(A2, 3, 4)

Substring from position 3

=TRIM(A2), =LOWER(A2), =PROPER(A2)

Clean and normalize case

=SUBSTITUTE(A2, "old", "new")

Replace literal text

=LEN(A2)

Character count

=CONCATENATE(A2, " ", B2) or A2 & " " & B2

Join strings

Date & time

=TODAY() / =NOW()

Current date / datetime

=DATE(2026, 7, 27)

Build a date from parts

=DATEDIF(A2, B2, "D")

Days between dates (M, Y too)

=EDATE(A2, 3)

Add 3 months to a date

=EOMONTH(A2, 0)

Last day of the month

=WEEKDAY(A2, 2)

Day of week (Mon = 1)

=WORKDAY(A2, 10)

10 business days later

=NETWORKDAYS(A2, B2)

Business days between dates

=TEXT(A2, "yyyy-mm-dd")

Format a date as text

=DATEVALUE("2026-07-27")

Parse text into a date

=A2 + 7

Dates are numbers: add days

=ISOWEEKNUM(A2)

ISO week number

Conditional logic

=IF(A2 > 100, "High", "Low")

Basic condition

=IFS(A2>90, "A", A2>80, "B", TRUE, "C")

Multi-branch without nesting

=SWITCH(A2, "US", "$", "EU", "€", "?")

Match against exact values

=AND(A2>0, B2<10) / =OR(...)

Combine conditions

=NOT(ISBLANK(A2))

Cell is not empty

=IFERROR(A2/B2, 0)

Fallback when a formula errors

=IFNA(VLOOKUP(...), "missing")

Fallback only for #N/A

=ISNUMBER(A2), =ISTEXT(A2), =ISDATE(A2)

Type checks

=LAMBDA(x, x * 2)(A2)

Inline reusable function

=LET(t, A2*B2, t + t*0.2)

Name a value, reuse it

Google-only functions

=GOOGLEFINANCE("GOOG")

Live stock price

=GOOGLEFINANCE("GOOG", "pe")

Specific attribute (P/E ratio)

=GOOGLEFINANCE("GOOG", "price", TODAY()-365, TODAY())

1-year price history

=GOOGLEFINANCE("CURRENCY:USDEUR")

Live exchange rate

=GOOGLETRANSLATE(A2, "en", "fr")

Translate cell text

=DETECTLANGUAGE(A2)

Guess the language code

=SPARKLINE(A2:A30)

In-cell line chart

=SPARKLINE(A2:A13, {"charttype","column"})

In-cell column chart

=SPARKLINE(A2, {"charttype","bar";"max",100})

In-cell progress bar

=IMAGE("https://x.com/logo.png")

Show an image in a cell

=HYPERLINK(url, "Open")

Clickable labelled link

Aggregation (SUMIFS & co.)

=SUMIF(A2:A, "Paid", C2:C)

Sum where one condition holds

=SUMIFS(C2:C, A2:A, "Paid", B2:B, ">100")

Sum with multiple conditions

=COUNTIF(A2:A, "Paid")

Count matching cells

=COUNTIFS(A2:A, "Paid", B2:B, ">=10")

Count with multiple conditions

=AVERAGEIFS(C2:C, A2:A, "Paid")

Conditional average

=MAXIFS(C2:C, A2:A, "Paid")

Conditional max (MINIFS too)

=SUMIFS(C:C, D:D, ">="&E1, D:D, "<"&F1)

Sum within a date window

=SUMPRODUCT((A2:A="X") * C2:C)

Array-style conditional sum

=COUNTIF(A2:A, "*inc*")

Wildcard match (* and ?)

=SUBTOTAL(109, C2:C100)

Sum ignoring hidden rows

Keyboard shortcuts

Ctrl+/ (Cmd+/)

Show all shortcuts

Ctrl+; / Ctrl+Shift+;

Insert date / time

Ctrl+D / Ctrl+R

Fill down / fill right

Ctrl+Enter

Fill selected range with input

Ctrl+Shift+V

Paste values only

Ctrl+Arrow

Jump to edge of data

Ctrl+Space / Shift+Space

Select column / row

Alt+Shift+= (rows/cols selected)

Insert rows or columns

F4

Cycle $A$1 reference locking

F2

Edit the active cell

Ctrl+Alt+M

Insert a comment

Alt+Down (on a filtered header)

Open the filter menu

Apps Script one-liners

Extensions > Apps Script

Open the script editor

const ss = SpreadsheetApp.getActiveSpreadsheet();

Current spreadsheet handle

const sheet = ss.getSheetByName("Data");

Grab a sheet by name

sheet.getRange("A1").setValue("Hi");

Write a cell

const rows = sheet.getDataRange().getValues();

Read all data as a 2D array

sheet.appendRow(["a", "b", new Date()]);

Append a row

sheet.getRange(1,1,data.length,data[0].length).setValues(data);

Bulk write (fast)

function onEdit(e) { ... }

Simple trigger on any edit

SpreadsheetApp.getUi().alert("Done");

Show a dialog

UrlFetchApp.fetch(url).getContentText();

HTTP request from a script

Triggers menu > Time-driven

Schedule a script (cron-like)

function DOUBLE(x) { return x * 2; }

Custom function: =DOUBLE(A1)

Hakuna kipengele kinacholingana na “:q”.

Kuhusu Karatasi ya Muhtasari ya Fomula za Google Sheets

Karatasi hii ya muhtasari ya fomula za Google Sheets inakusanya functions zinazofanya Sheets istahili kutumiwa kwenye ukurasa mmoja unaotafutika: function ya QUERY, ARRAYFORMULA na arrays, familia ya IMPORT, FILTER, SORT na UNIQUE, lookups, functions za maandishi, tarehe na saa, mantiki ya masharti, functions za Google tu, ujumlishaji kwa SUMIFS na wenzake, njia za mkato za kibodi, na mistari mifupi michache ya Apps Script.

Inaanza na fomula zisizo na sawa katika Excel (QUERY yenye sintaksia inayofanana na SQL, ARRAYFORMULA kutumia fomula chini ya safu nzima, IMPORTRANGE, IMPORTHTML na GOOGLEFINANCE) kisha inashughulikia VLOOKUP, XLOOKUP, INDEX/MATCH, TEXTJOIN, SUMIFS na hesabu za tarehe za kila siku.

Karatasi ni bure na inafanya kazi upande wa mteja: chuja safu moja kwa moja kwa kisanduku cha utafutaji, ruka kati ya sehemu kwa jedwali la yaliyomo linalobaki juu, nakili fomula yoyote kwa mbofyo mmoja na chapisha ukurasa kuwa rejeo la mezani.

Jinsi ya kutumia Karatasi ya Muhtasari ya Fomula za Google Sheets

Pitia sehemu kwa haraka, kuanzia QUERY function na ARRAYFORMULA kupitia Lookups hadi Apps Script one-liners.
Tafuta jina la function kama QUERY, FILTER au XLOOKUP ili kuchuja kila safu moja kwa moja.
Ruka hadi Google-only functions kwa fomula ambazo hazipo katika Excel.
Bofya fomula au ikoni yake ya kunakili ili kuinakili, kisha ibandike kwenye seli na uhariri safu zake.
Chapisha karatasi ili kupata rejeo la Google Sheets la nje ya mtandao.

Maswali yanayoulizwa mara kwa mara

Sehemu kumi na mbili: QUERY, ARRAYFORMULA na arrays, functions za IMPORT, FILTER, SORT na UNIQUE, lookups, functions za maandishi, tarehe na saa, mantiki ya masharti, functions za Google tu, ujumlishaji, njia za mkato za kibodi, na mistari mifupi ya Apps Script.

Ndiyo, kwanza na kwa kirefu: SELECT, WHERE, GROUP BY, ORDER BY, LIMIT, LABEL na sintaksia ya herufi za safu wima, kila moja kama fomula tayari kunakiliwa.

Hii inashughulikia fomula mahususi za Google (QUERY, ARRAYFORMULA, IMPORTRANGE, IMPORTHTML, GOOGLETRANSLATE, GOOGLEFINANCE na SPARKLINE) ambazo karatasi ya Excel haina sawa nazo.

Ndiyo. Bofya safu yoyote au ikoni yake ya kunakili na fomula inatua kwenye ubao wako wa kunakili, tayari kubandikwa kwenye seli.

Ndiyo, ni bure kabisa: linatafutika, linachapishika na linaonyeshwa kwenye kivinjari chako bila usajili.

Utafutaji maarufu

fomula za google sheets google sheets cheat sheet google sheets query function mifano ya arrayformula importrange google sheets njia za mkato za google sheets orodha ya fomula za sheets

Unahitaji msaada?

Umepata tatizo na zana hii? Tujulishe timu yetu.

Ripoti tatizo

Ongeza zana hii ya bure kwenye tovuti yako mwenyewe: nakili na ubandike msimbo ulio hapa chini.

<iframe src="https://tweakee.com/sw/tools/developer/google-sheets-cheatsheet?embed=1" width="100%" height="720" style="border:1px solid #e2e8f0;border-radius:12px;max-width:680px;" loading="lazy"></iframe>