Lecture 3. Google Sheet: Essential functions

Summary

Вторая часть лекции посвящена работе с практическими функциями в Google Sheets. Рассматриваются базовые функции, такие как сумма, максимум, минимум и среднее значение, а также примеры использования их для расчетов. Демонстрируется работа с функциями умножения и вычитания, а также более сложные задачи, такие как расчет общей стоимости, фильтрация данных и условное форматирование. Лекция также охватывает сортировку данных, создание выпадающих списков и фильтрацию данных для удобного анализа. Пример показывает, как использовать эти функции в Google Sheets и Excel.

Takeaways

• 😀 Для использования функций в таблицах необходимо начинать с символа равно.
• 💻 Функции SUM, MAX, MIN и AVERAGE используются для базовых математических операций с данными.
• 📊 Функция SUMPRODUCT помогает умножать значения столбцов, например, цены на количество.
• 📉 Для подсчета количества числовых данных используется функция COUNT, а для условного подсчета — COUNTIF.
• 🧮 В таблицах можно выполнять арифметические действия, такие как вычитание, умножение и деление.
• 📋 Сортировка и фильтрация данных позволяет выделить определенные данные на основе условий, например, фильтр по цене больше 65 000.
• 📋 Фильтрация с использованием Filter View удобна для работы с большими данными и сохранения результатов фильтрации.
• 📑 Условное форматирование помогает изменять цвет ячеек на основе определенных условий, например, цены ниже или выше заданного значения.
• 📅 Для автоматической генерации случайных данных используется функция RANDBETWEEN.
• 🖥 Функции Google Sheets аналогичны функциям Microsoft Excel и могут использоваться взаимозаменяемо.

Q & A

Какие основные функции были рассмотрены в этом видео?

Основные функции, рассмотренные в видео, включают: SUM (сумма), MAX (максимум), MIN (минимум) и AVERAGE (среднее значение).

Что означает функция SUM и как ее использовать?

Функция SUM используется для вычисления суммы чисел в заданном диапазоне. Чтобы ее использовать, нужно ввести знак равно '=', написать 'SUM' и выделить диапазон чисел, с которыми хотите работать.

Как вычислить разницу между максимальной и минимальной ценой в таблице?

Для вычисления разницы нужно сначала найти максимальное и минимальное значение с помощью функций MAX и MIN, а затем вычесть минимальное значение из максимального с использованием оператора '-'.

Как работает функция SUMPRODUCT?

Функция SUMPRODUCT используется для умножения элементов двух или более столбцов и их последующего суммирования. В примере видео она применяется для расчета общей стоимости, умножая цену на количество товаров.

Как можно подсчитать количество устройств в таблице?

Для подсчета количества устройств можно использовать функцию COUNT, которая работает только с числовыми значениями. Для этого выделите столбец с количествами и примените функцию COUNT.

Что делает функция COUNTIF?

Функция COUNTIF считает количество ячеек в диапазоне, которые соответствуют определенному условию. Например, можно подсчитать, сколько устройств в таблице имеют количество больше 12, применяя функцию COUNTIF с условием '>12'.

Как создать выпадающий список в таблице?

Чтобы создать выпадающий список, нужно выбрать диапазон ячеек, затем зайти в 'Data' -> 'Data validation', изменить критерий на 'List of items', ввести необходимые элементы и сохранить изменения.

Что такое условное форматирование и как оно применяется?

Условное форматирование позволяет менять цвет ячеек в зависимости от их значения. Например, можно задать правило: если значение меньше 75,000, ячейка будет окрашена в зеленый цвет.

Как можно отфильтровать данные по определенным критериям?

Для фильтрации данных нужно выделить заголовки столбцов и нажать на кнопку фильтра. После этого можно выбрать необходимые условия фильтрации, например, показывать только товары от конкретной компании с ценой выше определенного значения.

Как сохранить результаты фильтрации с помощью Filter View?

Чтобы сохранить результаты фильтрации, можно использовать 'Filter View'. Это позволяет сохранить настройки фильтра и к ним можно будет вернуться позже, не изменяя основную таблицу.

Outlines

00:00

📝 Введение и базовые функции

В первой части видео начинается практическое занятие по таблицам, где представлены различные устройства с их ценами, количеством, и другими данными. Ведущий объясняет, как использовать базовые функции: сумму, максимум, минимум и среднее. Рассмотрены примеры применения функций на столбце цен, а также их использование для проведения простых вычислений. В частности, обсуждается, как найти сумму цен, максимальную и минимальную стоимость, а также как вычесть минимальную цену из максимальной.

05:02

📊 Подсчет устройств и функция COUNTIF

Во втором параграфе объясняется использование функции COUNT для подсчета количества устройств в таблице, которая возвращает только числовые значения. Также вводится функция COUNTIF, которая позволяет считать значения на основе условий, например, сколько устройств имеют количество больше 12. Рассмотрен пример, где учитываются условия '>12' и '>=12'. Также обсуждается создание нового столбца для расчета разницы в ценах между 2021 и 2022 годами с помощью функции RAND для генерации случайных данных.

10:05

🎨 Условное форматирование

Этот параграф посвящен условному форматированию. Рассматривается, как форматировать ячейки на основе условий. Например, если цена устройства меньше 75 000, ячейка окрашивается в зеленый цвет, если больше 250 000 — в желтый, а если цена между этими значениями — в красный. Ведущий показывает, как добавить несколько правил форматирования и как изменения данных влияют на форматирование. Также упоминается, что условное форматирование может быть использовано для создания визуальных эффектов, например, с помощью смайликов.

15:05

🔍 Фильтры и сортировка

В этом параграфе рассказывается о применении фильтров для работы с данными. Показано, как установить фильтры на основе условий, например, отфильтровать данные по компании и цене. Описываются два метода: использование стандартных фильтров и фильтров вида (Filter View), который позволяет сохранить фильтры для повторного использования. Ведущий объясняет, как это может сэкономить время, если необходимо часто возвращаться к результатам фильтрации. Завершается параграф объяснением сортировки данных по алфавиту и в обратном порядке.

Keywords

💡Таблица

Таблица – это структура данных, представленная в виде строк и столбцов, используемая для хранения и организации информации. В данном видео таблица содержит данные о разных устройствах (камеры, мобильные телефоны, ноутбуки), их цены, компании, которые их выпускают, и города, где они расположены. Пример: видео объясняет, как использовать функции для вычисления суммы цен в колонке таблицы.

💡Функции

Функции – это предопределенные формулы, которые выполняют определенные действия над данными в таблице. В видео рассматриваются функции, такие как SUM, MAX, MIN и AVERAGE, которые используются для вычисления суммы, максимальной, минимальной цены и среднего значения. Например, функция SUM используется для вычисления общей стоимости всех устройств в таблице.

💡Максимум (MAX)

Функция MAX используется для нахождения максимального значения в диапазоне данных. В видео она применяется для нахождения самого дорогого устройства в таблице. Пример: функция MAX была использована для того, чтобы определить, что HP Pro – это самое дорогое устройство в таблице с ценой в один миллион.

💡Минимум (MIN)

Функция MIN служит для определения минимального значения в наборе данных. В видео её используют для нахождения самого дешёвого устройства. Пример: функция MIN была использована для нахождения флеш-накопителя USB, цена которого составляет 3000 тенге.

💡Среднее (AVERAGE)

Функция AVERAGE вычисляет среднее арифметическое значение диапазона данных. В видео она применяется для вычисления средней цены всех устройств. Это помогает лучше понимать общую картину цен на устройства.

💡Умножение

Умножение – это математическая операция, используемая для расчёта общей стоимости устройств, умножая цену каждого устройства на его количество. В видео показано, как умножить цену на количество для вычисления общей стоимости всех устройств в таблице.

💡Условное форматирование

Условное форматирование – это инструмент, который изменяет внешний вид ячеек таблицы в зависимости от их значений. В видео демонстрируется, как применить условное форматирование для выделения цен, меньше 75 000 тенге зелёным цветом, и цен, превышающих 250 000 тенге, жёлтым цветом. Это помогает легко идентифицировать данные, соответствующие определённым условиям.

💡Фильтрация

Фильтрация – это процесс отбора данных, соответствующих определённым условиям, чтобы упростить работу с таблицей. В видео показано, как с помощью фильтрации оставить только устройства компании 'Sopak' с ценой более 65 000 тенге, что позволяет сосредоточиться только на нужных данных.

💡Сортировка

Сортировка – это процесс упорядочивания данных в таблице по возрастанию или убыванию. В видео сортировка демонстрируется на примере сортировки названий устройств в алфавитном порядке. Этот метод помогает организовать данные для более удобного анализа.

💡Случайные числа (RANDBETWEEN)

Функция RANDBETWEEN генерирует случайное число в заданном диапазоне. В видео её используют для заполнения столбца 'Цена 2022' случайными числами от 50 000 до 400 000 тенге, чтобы продемонстрировать изменения цен между 2021 и 2022 годами. Это удобный способ создавать тестовые данные для анализа.

Highlights

Introduction to practical functions in a table with fictional data including devices, prices, companies, cities, and quantities.

Basic functions such as SUM, MAX, MIN, and AVERAGE are explained with examples from the price column.

Step-by-step guide on calculating the difference between the maximum and minimum prices using subtraction.

Demonstration of how to calculate the total price using both manual multiplication and the SUMPRODUCT function.

Explanation of the COUNT function and how it is applied to count the number of devices in the table.

Introduction of the COUNTIF function to count the number of devices where the quantity is greater than 12.

Randomized numbers are generated for a new column, and the difference between prices from 2021 and 2022 is calculated.

Demonstration of creating a drop-down list using the Data Validation tool and applying it to a new column.

Conditional formatting is used to apply different colors to the price column based on specific conditions.

Introduction of filtering techniques, including filtering by company name and price greater than a certain value.

Explanation of the filter view option, which allows users to save filtered results for future use.

Sorting devices in ascending and descending order by device name.

Comparison between Google Sheets and Excel, emphasizing the similarity in working principles.

Using filter view for repeated tasks to save time and effort, making it more convenient than regular filtering.

Wrap-up and preview of the next session, which will continue with more advanced functions in Google Sheets.

Transcripts

okay let's start this is the second part

of our election number four where we're

going to focus more on practical part so

first let's have a look here we have one

table which shows us different devices

like cameras mobile phones laptops their

prices and the company with this device

is available and at the same time the

city where this company is located and

of course the quantity of given devices

and the radio code is empty right now

which we're going to feel a little bit

later okay this is like fictional data

that is why I don't pay more attention

guys I mean the price is fictional one

and some data is also fictional so now

we should start learning how to apply

functions so we will start from the

basic functions let's uh Max mean and

average and then we will increase our

levels so let's start from the sum okay

so in order to start working with the

functions first you should type all time

equal sign after that we will type our

function sum and then every function

takes at least one argument inside so

you can also click this question mark to

see what kind of argument can accept

this given function so in my case I'm

working with a price column that's why

I'm going to highlight the price column

here please highlight on the numeric

data do not include the title okay press

the enter button this is our result okay

the same with the max I'm going to type

here the function Max

okay the max function will return as the

most expensive device in this table okay

I'm going to highlight the price column

one more time press the enter button so

we have got the number one million which

is HP Pro okay this is our most

expensive product in the given table so

the minimum function equal to mean again

highlight your price column one more

time press the enter we have got the

number three thousand singing which is

USB flash

okay and the last one here average let

me type here the function average as you

see here on the top side Google already

suggests us what column we should

highlight so I'm going to just click it

press the enter okay this is the number

I have got at the output

great so in that point we have finished

the basic four functions which are

really easy to use and now we will do

some basic calculations like

subtractions division multiplication so

our first task is to subtract minimum

number from the maximum number again I'm

going to type here equal sign so since I

already have calculated Max and mean

values I will just click here to get the

address of the given cell minus minimum

press enter that's it so now let's

imagine we don't have a maximum values

which has been calculated before in a

separate way so I have to calculate

again here okay let's type here the max

okay I'm going to highlight this price

column one more time

close the brackets minus mean function

uh again I have to highlight the price

column one more time press into button

so that's it so this is the same with

the previous one equal to Maxwell we

already have the business slash number

two plus average number

and now we should calculate the total

price okay so here we already have got

the value for the sum the main

difference between sound and total price

so here in some case you have calculated

only prices and now in case of total

price uh you have to pay attention for

Price column at the same time the

quantity is column okay let's do that so

in order to calculate total price let me

show first the manual way and after that

I will show you how we can do it with a

function

so first we should do some

multiplication which means what I have

to get an address of the price for one

action camera which is 35 000 again and

multiplied by quantity number five okay

so this is the price for five action

cameras so now I have to do the same for

other devices so to do that I will just

select the cell click this small blue

rectangle hold and drag it down to

multiply uh price by quantitative or for

other devices okay this is just my

multiplication so now in order to get a

value for total price I should calculate

here sum of given multiplications okay

that's our result which is approximately

58 million teen Gap okay so let me do

this total price with function right now

let's type here the function which is

called sum product where product means

multiplication the same again I'm going

to work with a price column common and

quantities column press enter here we

are resolve a same so two different ways

okay so next task here we should start

count counting the number of devices in

our table okay let's say here I don't

have numeric values let me delete it

till it start counting equal to count

will return numeric value and also it

works only with numeric values so you

can work with price column or you can

continue working with quantities columns

so I will work with the quantity let me

highlight this quantities column press

the enter button okay now I have got the

number 20. this number 20 means we have

in this table 20 devices so you can

check it without a feeling also number

one and number two here highlight these

two numbers and and drag it down so here

we are we can say 20 and 20 is here so

this has got Auto feeling guys so now

let's add condition to our account

function which will be like column Eve

right now equal to I'm going to type one

more time my function in case of count

if this function will take two arguments

first argument is the range second

argument is the criteria so let's say

our task is to count the number of

devices where your quantity is greater

than 12. again I'm going to work with

quantities column that's what I will

highlight this column AR let's open up

the quotation mark and inside we should

write down our condition which is

greater than number 12. press the into

button so this is our result number two

so just have a look HP Pro the quantity

is 34 which is greater than 12. at the

same time iPhone 7 plus quantity is 14

which is also greater than number 12. so

what about Samsung Galaxy so here we

have a number 12. so number 12 it is

equal to 12 it's not the greater than

12. so that is why if you would like to

include the number 12 in your condition

you should also indicate here greater

than or equal to sign okay so in this

case output will be number three so now

let's do one more task for the

subtraction let's say this is my price

for

2021. so I'm going to create here new

column and let me rename it like price

2022 and in order to save our time let's

just randomize numbers okay I'm going to

type here the function run between and

just have a look this function uh takes

two arguments which is first one the

lowest number second one is the highest

number at the lowest number I am going

to indicate here 50

000 and the highest number will be for

example four hundred and thousands press

the insert

okay so here we are this is our randomly

generated number you and me have a

different vows it's okay let me scroll

it down to get the values for my other

cells

that's it so now let's say our task is

to uh calculate the difference between

these two prices in two given years I am

going to create here new column let's

rename it like differences

so you can rename it as you want so

let's do in this way let's take the

address of last price minus the previous

price press the enter uh since it is the

positive number it means the price was

increased if it is negative number here

we can say the price decreased okay this

is like a simple example of course don't

pay attention for the prices because

they are fictional guys so next task

let's move down and here let me start

from the downside okay let's start from

drop down list so since we have here the

radial column which is empty our task is

to fill out this column with drop down

first let me highlight this region

column here and let's go for the data

inside you may see data validation since

I'm going to create a drop down list I

should change the criteria to list of

items and let's provide few different

items so you can type here different

regions in Kazakhstan so I will type

here the basic ones like this where

shows numbers for example and the

central click the save button okay I

have got here the small triangles if you

click you can see here the list of items

and then you can easily fill your table

from that provided list next one here we

should move for a conditional formatting

as it name says so here you will format

your cells based on your different

conditions let me highlight it click the

right side of your mouse so here you can

see conditional formatting let's click

it okay in this appeared window you can

see here applied to range which is our

price column which we already have

highlighted so here I'm going to select

one condition which is let's say less

than so let me provide here one number

if my price is less than 75

000 tenge in this case color of myself

should be green one to seven we already

have got green colored cells so if I

will change this number let's say 76

000 thing

color also has changed because this

number is greater than our conditional

number okay let me click here add

another Rule and now I'm going to apply

the rule which is greater than let's say

my number should be greater than 250 000

in here in this case color should be for

example yellow okay now I have got the

yellow color okay now let's apply the

third row at another rule here the third

rule is going to be is between so let's

provide our first number 75 000 single

and the second number it was 250 000 if

the price is between these two then

color for example should be red one

click the done and have a look so we

have a three conditions here based on

our condition we have got three

different colors again if I will change

here the number

color also had changed okay please check

in your case too so this is called

conditional formatting so by the way you

can also create this kind of emojis or

even cartoon based characters with the

help of conditional formatting where you

have to apply different uh numbers and

based on your numbers and conditions for

money will fill out with the colors okay

it will take just few minutes okay let's

go back let me close this window so our

next task it will be for filter so let's

have a look at the first task with the

help of filter our task is to live on

the devices where company name is supak

and price more than 65 000 thing yeah in

order to install the filter first you

should highlight your header section

click the screw the filter button after

that you can see the small triangles

according to my task company should be

only so bad so here you can see techno

Dome Forest comb Ruba in order to live

only two packs I have to go here click

it and you can work in two ways if your

table is small as in our case so you can

just clear everything here and select

only toolbox like this in case if your

table consists of a lot of data then

this way won't be really convenient for

you in this kit you should click this

filter by condition and select here your

conditions based on your task click OK

button

so now I have got only two pack here so

here in my chart I also have a second

condition where price should be greater

than 65 000 okay I will go here one more

time you can also clear everything and

select the numbers which are greater

than 65 000 but it's not really

convenient when it comes to numbers guys

instead I can go here filter by

condition click here create a Zen and

I'm going to type here 65 000 singer

Presto cable battery okay here we are

this is our result okay so now I'm going

to copy my result and paste on the

downside a little bit later I will also

explain you how you can save this filter

results with the help of interview okay

so now let's say I have to start working

with my second task however here I

cannot see all my table there is all my

20 devices I can see on the solve that

so which means you have to delete all

previously applied filters to return

back your table's first version okay I'm

going to go for the price because the

great exam and select the first one to

delete all applied filter conditions

click the key button let's go for the

company and again I'm going to select

all items here press OK one okay so now

I have got all my 20 devices so now let

me show how you can do the first task

with the help of filter view which is

really convenient go here for data

section you can see here field of views

create new filter View

okay then you will see here black color

it Rose and column and let me do my

first task one more time okay let's go

for a company here I'm going to clear

everything and select on the suit back

okay button uh let's go for the price

one more time filter by condition should

be greater than and type here 65 000

sing yeah this is my result from Fields

review so now on the top side you can

also rename it let's type here example

number one okay and now I'm going to

close this filter View

when you will close it your tables

previous first version will be returned

back automatically which means there in

order to spend time to delete all your

previously applied filters which is

really comfortable in order to see your

previously applied peer-to-view results

you can anytime go for a data section

just have a look here fields of views

and here you can see example number one

which shows your result directly so now

if I will again change the data here all

my changes based on my task will be also

applied in my future view result that is

why when it comes to field series if

you're using the filter only one time

okay you can do it with just filter to

get a result and return back but in case

if you're going to work with the filter

result every time after one day after

one week and then I highly recommend you

to use the future view so now I'm going

to show you the last task how we can

sort device names in descending and

ascending orders from a till that or

from satellite okay it's really easy

just click this button so here you can

see the sort from Agents that if you

click this one you can see your device

has ordered in alphabetical order or you

can do vice versa like this so okay it

was just an introduction to Google sheet

all the things that we have learned from

that video you can also apply in Excel

okay the working principles are same so

next week we will continue the topic

with Google sheet and will be another

task with another functions