Lecture 3. Google Sheet: Essential functions
Summary
TLDRВторая часть лекции посвящена работе с практическими функциями в 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
📝 Введение и базовые функции
В первой части видео начинается практическое занятие по таблицам, где представлены различные устройства с их ценами, количеством, и другими данными. Ведущий объясняет, как использовать базовые функции: сумму, максимум, минимум и среднее. Рассмотрены примеры применения функций на столбце цен, а также их использование для проведения простых вычислений. В частности, обсуждается, как найти сумму цен, максимальную и минимальную стоимость, а также как вычесть минимальную цену из максимальной.
📊 Подсчет устройств и функция COUNTIF
Во втором параграфе объясняется использование функции COUNT для подсчета количества устройств в таблице, которая возвращает только числовые значения. Также вводится функция COUNTIF, которая позволяет считать значения на основе условий, например, сколько устройств имеют количество больше 12. Рассмотрен пример, где учитываются условия '>12' и '>=12'. Также обсуждается создание нового столбца для расчета разницы в ценах между 2021 и 2022 годами с помощью функции RAND для генерации случайных данных.
🎨 Условное форматирование
Этот параграф посвящен условному форматированию. Рассматривается, как форматировать ячейки на основе условий. Например, если цена устройства меньше 75 000, ячейка окрашивается в зеленый цвет, если больше 250 000 — в желтый, а если цена между этими значениями — в красный. Ведущий показывает, как добавить несколько правил форматирования и как изменения данных влияют на форматирование. Также упоминается, что условное форматирование может быть использовано для создания визуальных эффектов, например, с помощью смайликов.
🔍 Фильтры и сортировка
В этом параграфе рассказывается о применении фильтров для работы с данными. Показано, как установить фильтры на основе условий, например, отфильтровать данные по компании и цене. Описываются два метода: использование стандартных фильтров и фильтров вида (Filter View), который позволяет сохранить фильтры для повторного использования. Ведущий объясняет, как это может сэкономить время, если необходимо часто возвращаться к результатам фильтрации. Завершается параграф объяснением сортировки данных по алфавиту и в обратном порядке.
Mindmap
Keywords
💡Таблица
💡Функции
💡Максимум (MAX)
💡Минимум (MIN)
💡Среднее (AVERAGE)
💡Умножение
💡Условное форматирование
💡Фильтрация
💡Сортировка
💡Случайные числа (RANDBETWEEN)
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
5.0 / 5 (0 votes)