Download "Умная таблица в Excel ➤ Секреты эффективной работы"

Please wait. We're preparing links for easy ad-free video watching and downloading.

Previous video: Сводные таблицы Excel с нуля до профи за полчаса + Дэшборды! | 1-ое Видео курса "Сводные Таблицы"Next video: 5 СОВЕТОВ💡 по оформлению таблицы в Excel

0:00

Введение - преимущества умных таблиц

0:56

Как сделать таблицу в Excel

1:54

Форматирование таблицы в Excel (Стили)

3:11

Имя (название) умной таблицы

4:09

Сортировка, фильтр и срез в умных таблицах

6:20

Строка итогов

7:16

Как добавить строку или столбец в умную таблицу

8:26

Как в таблице перенести строку или столбец

9:14

Как удалить дубликаты в умной таблице

10:23

Сводная таблица в Excel

11:40

Умные таблицы и диаграммы

12:20

Особенности проведения расчетов в умных таблицах

15:14

Как удалить умную таблицу

5 СОВЕТОВ💡 по оформлению таблицы в Excel

5 СОВЕТОВ💡 по оформлению таблицы в Excel

Channel: Андрей Сухов

Сводные таблицы Excel с нуля до профи за полчаса + Дэшборды! | 1-ое Видео курса "Сводные Таблицы"

Сводные таблицы Excel с нуля до профи за полчаса + Дэшборды! | 1-ое Видео курса "Сводные Таблицы"

Channel: Билял Хасенов – Excel, VBA & More

Учимся работать с умными таблицами в Excel. Интенсив о работе в Excel

Учимся работать с умными таблицами в Excel. Интенсив о работе в Excel

Channel: Skillbox Управление

Как сделать сводную таблицу в Excel

Как сделать сводную таблицу в Excel

Channel: Езепов Дмитрий

Excel для работы: экономим время. Интенсив о работе в Excel

Excel для работы: экономим время. Интенсив о работе в Excel

Channel: Skillbox Управление

Формулы и простые функции в Excel. Интенсив по быстрой работе в Excel

Формулы и простые функции в Excel. Интенсив по быстрой работе в Excel

Channel: Skillbox Управление

как создать таблицу в excel

как создать таблицу в excel

Channel: Обучение Microsoft Office. Цифровой помощник

Как создать таблицу в excel / Excel по - простому

Как создать таблицу в excel / Excel по - простому

Channel: Excel по - простому

Как эффективно работать в Excel? Лайфхаки и умные таблицы. Вебинар по Excel

Как эффективно работать в Excel? Лайфхаки и умные таблицы. Вебинар по Excel

Channel: Skillbox Управление

Разбор трюков в Excel. Интенсив по Excel

Разбор трюков в Excel. Интенсив по Excel

Channel: Skillbox Управление

умная таблица в excel

умные таблицы в excel

создание таблицы в excel

как создать таблицу в excel

как сделать умную таблицу в excel

создание умной таблицы в excel

умная таблица Excel

умные таблицы в эксель

умные таблицы excel

умная таблица

как сделать таблицу в excel

таблица в excel

форматирование таблицы в excel

срез excel

как в экселе сделать таблицу

таблица эксель

excel таблицы

таблица excel

сводные таблицы excel

Андрей Сухов

уроки excel

00:00:02

Excel quite a long time ago, however, many

00:00:04

users still do not use them,

00:00:06

preferring to format data using

00:00:08

other standard

00:00:10

Excel formatting tools.

00:00:12

However, such tables are not without reason

00:00:14

called smart, because with their help you can

00:00:17

not only quickly format a range with

00:00:19

data by applying one of the

00:00:21

standard or custom styles to it,

00:00:23

they also allow you to

00:00:24

simplify and speed up the calculation as much as possible. We

00:00:27

are talking here not only about the fact that you do not need to

00:00:29

manually copy the formula throughout the entire

00:00:30

column, since in the table this operation

00:00:33

is performed automatically; first of all, it

00:00:35

is important that that the

00:00:37

calculation algorithm in smart tables is

00:00:38

optimized, it allows you to perform

00:00:41

calculations faster, which will be noticeable when

00:00:43

working with large amounts of data

00:00:45

that consist of thousands of rows and

00:00:47

columns, this video is a kind of

00:00:49

instruction for complete tables and I hope that

00:00:51

with its help you will be able to increase the

00:00:53

efficiency of your work in

00:00:55

Excel and so let's start creating a table in

00:00:58

Excel, like many other things in Excel, you

00:01:00

can create a table in several

00:01:02

ways, if you already have some

00:01:05

continuous range of values, then

00:01:07

just place the table cursor in

00:01:09

any of its cells and select the

00:01:11

appropriate tool on the insert tab, you

00:01:14

can also simply press the

00:01:16

kontrol key combination then the same window will open,

00:01:20

if the range already has a line with

00:01:22

headings, then it’s worth making sure that the

00:01:24

corresponding checkbox is checked;

00:01:27

in this case, Excel will automatically

00:01:29

turn the first line of the range into a

00:01:31

heading line;

00:01:33

if this is not done, then

00:01:36

another row will be created above and that’s what

00:01:39

Excel will use consider it a header row,

00:01:41

if there is no data on the sheet and you need to

00:01:43

create an empty table, then first

00:01:45

select the desired range of cells and then

00:01:48

use a keyboard shortcut or the

00:01:50

appropriate tool from the

00:01:52

insert tab

00:01:55

when creating a table it will be assigned and

00:01:58

the standard formatting

00:01:59

can always be changed using the

00:02:01

corresponding toolbar

00:02:03

here It is worth considering that the table

00:02:06

is an object, which means it is not

00:02:08

necessary to select a weight and range to

00:02:10

change the formatting; just

00:02:12

set the table cursor in any of the

00:02:14

table cells and the formatting will

00:02:16

apply to the entire object

00:02:18

and not just the active cell. In addition to

00:02:22

ready-made styles, it is possible to create

00:02:24

your own table style when This will open a

00:02:27

window in which you can customize

00:02:29

each of the table elements in detail, here you can

00:02:31

specify the

00:02:32

borders and fill and font parameters and

00:02:35

make other settings. I did

00:02:39

n’t have to resort to such scrupulous settings, I always made do with

00:02:41

standard styles,

00:02:43

they can be supplemented with

00:02:45

formatting settings that are available in the

00:02:47

panel with style parameters here you can

00:02:49

manage the

00:02:50

formatting elements by turning on or

00:02:52

off the corresponding checkbox and

00:02:55

select simple formatting in the form of a gray

00:02:57

grid with headings on a gray background,

00:03:01

then turn off the display of the grid on the sheet

00:03:03

so that only the table is visible, now the

00:03:06

grid of cells on the sheet is not distracting and

00:03:08

further operations will be more clear

00:03:12

as I mentioned at the beginning of the video, the smart

00:03:15

table is an Axel object, which

00:03:18

means that to identify each

00:03:20

table, a unique name is assigned; this

00:03:23

name is subsequently used in formulas; the

00:03:26

name is displayed in the corresponding field

00:03:28

on the contextual tabs; let me

00:03:30

remind you that contextual tabs

00:03:31

appear when you select the corresponding

00:03:34

object on the excel sheet; by default, xr is

00:03:37

assigned to the standard name table with an

00:03:39

ordinal number in my case this

00:03:42

table is assigned an ordinal number one

00:03:44

table name can be changed to

00:03:46

arbitrary; this is not necessary;

00:03:48

however, if there are several tables in the document, the

00:03:50

data from which will be

00:03:52

connected by formulas, then human

00:03:54

names will greatly simplify the work; for

00:03:57

this reason, I recommend assigning

00:03:59

It is the tables that can rely

00:04:01

on the nature of the data that is in these

00:04:03

tables, for example, we

00:04:06

have this table a list of orders.

00:04:10

Of course, you noticed

00:04:13

that in the table headers there are

00:04:15

filter buttons, this is a standard for smart tables and the

00:04:18

filtering option is enabled by

00:04:20

default if filters are not needed then they

00:04:23

can be hidden using the appropriate

00:04:24

checkbox it is contextual tabs

00:04:26

constructor the filter button allows you to

00:04:28

very quickly sort

00:04:30

or filter data by the contents of

00:04:33

any column

00:04:34

this is a very convenient function that

00:04:36

saves a lot of time when working with

00:04:38

large amounts of data

00:04:40

filters are available not only in smart

00:04:42

tables and even in a regular range

00:04:44

you can enable a filter for headings; it

00:04:47

will have exactly the same functionality;

00:04:48

for example, excel automatically groups

00:04:51

dates by month;

00:04:52

also in the additional menu there are

00:04:54

other operations that allow you

00:04:56

to filter data

00:04:58

by time as accurately as possible; the enabled

00:05:01

filter is displayed on the button; to

00:05:03

remove it, you need to select the

00:05:05

appropriate option; I will change it

00:05:08

if unique There is a lot of data in the columns, so it

00:05:11

can be very difficult to quickly find the desired value.

00:05:14

In this case,

00:05:16

the search will come to the rescue; it allows you to display

00:05:18

only those values that satisfy the

00:05:21

entered characters. But if the table is

00:05:24

large and it does not fit in height on

00:05:26

one screen, then when you scroll, the header

00:05:29

will move into the area the names of the columns of the

00:05:31

sheet,

00:05:32

that is, when scrolling the table, they will be

00:05:33

visible and both the headers themselves and filters will be

00:05:36

available, which is extremely convenient because there is

00:05:38

no need to fix the

00:05:40

table header when working with data in it, in addition to

00:05:43

filters in the headers, you can insert so-

00:05:45

called slices, in fact, this is also a filter

00:05:48

that has our own individual

00:05:50

interface, for example, we need to get

00:05:52

data for individual managers each

00:05:55

time selecting managers in filters is very

00:05:57

inconvenient; it’s

00:06:02

much easier to insert a slice on the manager column. A

00:06:06

separate panel will appear in the window, which will

00:06:09

list the unique

00:06:11

values of the column, in our case, the

00:06:13

names of managers; by clicking on the desired last

00:06:16

name, we will get the data only by its

00:06:18

operation, in addition to filters on the

00:06:24

toolbar with style parameters, you can

00:06:26

turn on and the totals line in it

00:06:28

will automatically calculate the sum

00:06:30

for the last column of the table if

00:06:33

you move the mouse pointer to this value, a

00:06:35

button will appear; it calls up a

00:06:38

context menu with available functions

00:06:40

except for summing in the totals line we

00:06:42

we can display the average value of

00:06:44

the number of values in the column number of

00:06:46

numbers, maximum or

00:06:48

minimum values, or, if

00:06:50

necessary, create your own formula

00:06:53

by calling the function insertion window. The same

00:06:56

can be done for any other

00:06:58

column in the totals line,

00:07:03

when using filters or slicers, the

00:07:06

totals line will take into account only the

00:07:08

values displayed on the screen as a

00:07:11

result, we can very quickly obtain

00:07:13

summary information on the positions we are interested in.

00:07:17

Another advantage of smart tables

00:07:19

is that they automatically

00:07:21

pick up newly entered data, that

00:07:24

is, if we start entering data next to the smart table in the

00:07:26

nearest column, the

00:07:28

table will automatically expand

00:07:31

to a new column the

00:07:33

same formatting will be applied in the same way

00:07:36

with a new row, although there is one more

00:07:39

nuance: if a formula is calculated in one of the columns,

00:07:42

it will automatically

00:07:44

spread to the new row; the

00:07:47

smart table will also automatically pick up the

00:07:49

data that we paste from the clipboard,

00:07:52

for example, copy this from the range and

00:07:55

insert them into the row that is located

00:07:58

below the smart table, the data will be

00:08:01

formatted and the corresponding

00:08:03

formulas will be calculated to expand the boundaries of the urea

00:08:06

table, you can manually do this by

00:08:08

grabbing the mouse with the marker that

00:08:10

is located in the lower right corner of the table and

00:08:12

moving it to the required number of

00:08:14

cells

00:08:15

in this way you can expand

00:08:27

Smart tables allow you to quickly change the

00:08:30

position of rows or columns within the

00:08:33

table. To do this, you need to place

00:08:34

the table cursor in the header of the column

00:08:36

that you want to move and then

00:08:39

move the pointer to the cell border. The

00:08:41

pointer will change its appearance to the

00:08:44

appropriate one and you can grab

00:08:46

the header with the mouse and automatically

00:08:48

select it. the entire column of smart tables,

00:08:51

we move the column to the desired position,

00:08:54

and when selecting a row, we

00:08:58

automatically select columns or

00:09:00

rows within smart tables,

00:09:02

allowing you to significantly reduce the time for

00:09:04

selecting the necessary ranges, and

00:09:06

also eliminates the movement of data

00:09:09

outside the table,

00:09:10

that is, in other sheet cells that are

00:09:12

located nearby

00:09:14

since smart tables are

00:09:16

an object, there is no need to completely

00:09:18

select their range when performing

00:09:21

some operations; just set the

00:09:23

table cursor in any of the

00:09:25

table cells and then turn to the

00:09:27

appropriate tool, so

00:09:30

you can

00:09:31

steal the table from repeating

00:09:33

values in a couple of mouse clicks;

00:09:34

select the appropriate tool on the

00:09:36

contextual ones tabs designer and then

00:09:39

indicate the column that needs to be

00:09:41

examined for duplicates, rows

00:09:44

with duplicates will be deleted, however this will

00:09:47

not affect the tables or data

00:09:50

that are located next to the table.

00:09:52

By the way, this tool is very convenient to

00:09:54

use to obtain a list of

00:09:56

unique values, remove the duplicate from the

00:09:59

corresponding column,

00:10:01

then copy the remaining one values to the

00:10:04

clipboard

00:10:06

and cancel the delete operation. Now you

00:10:13

can paste unique values from the

00:10:15

clipboard into another table.

00:10:18

The same can be done using

00:10:20

other tools. For example, using

00:10:22

pivot tables, a

00:10:27

separate tool has been added to the contextual constructors tab that

00:10:29

allows you to quickly create a pivot table

00:10:31

based on data. from a

00:10:33

smart table, pivot tables are a powerful

00:10:36

Excel tool and I dedicated

00:10:38

several videos to them, as well as a separate

00:10:40

large video course, I will leave all the links in the

00:10:42

description under this video when creating

00:10:45

pivot tables based on data from

00:10:47

smart tables,

00:10:49

not a certain range of cells will be displayed in the corresponding field

00:10:52

and for the name of the smart table we gave earlier,

00:10:55

this means that when adding new

00:10:57

data to the table, it will be automatically

00:11:00

picked up by the original table, we will not

00:11:03

need to manually expand the range

00:11:04

to new data, let's create a list of

00:11:08

unique products and calculate the amount of

00:11:10

orders for each of them if we now

00:11:13

add new ones data into the original table,

00:11:16

then after moving to the pivot table

00:11:19

you will only need to update it with

00:11:22

new data automatically pulled up

00:11:25

from smart tables.

00:11:26

This approach allows you to greatly

00:11:28

simplify the work.

00:11:29

Apparently, I need to monitor the range of

00:11:31

values on the basis of which the

00:11:33

pivot table is built, and the probability of not

00:11:35

taking into account the newly entered data tends

00:11:37

to zero, the same applies to charts;

00:11:41

charts in Excel are built on the basis of a

00:11:43

certain range of data and also

00:11:46

directly depend on the selected range;

00:11:49

entering new data into the usual range will

00:11:52

not appear in the chart in any way; you

00:11:54

will need to update the information in the

00:11:56

chart settings

00:11:57

and expand the range to new data;

00:12:00

smart tables allow you not to think

00:12:03

about the pre-selected range,

00:12:05

since new data will be automatically

00:12:07

picked up in the table and also

00:12:09

automatically displayed on the diagram;

00:12:14

I have dedicated a separate large video course to the nuances of working with charts and graphs; all the

00:12:16

links are in the description under this

00:12:19

video; and since smart

00:12:22

tables are separate

00:12:24

Excel objects, then and addressing in cells has

00:12:27

its own characteristics within the table, the

00:12:29

formulas will not involve

00:12:31

relative links, as is usually the

00:12:33

case when calculating on an excel sheet,

00:12:35

but links to specific columns, smart

00:12:37

tables in square brackets, the

00:12:40

names of the headings of the corresponding

00:12:42

columns are indicated, the dog symbol indicates

00:12:45

that the data is taken from the same

00:12:47

line in which the formula is entered, as

00:12:50

a result, the formula will be automatically

00:12:52

copied to all cells of the column,

00:12:54

regardless of its size. On our

00:12:57

part, we will not need to do anything additional.

00:12:59

Another advantage of

00:13:01

smart tables is that we can

00:13:04

very easily use the data from them in

00:13:06

formulas that located

00:13:08

outside the smart tables or even on

00:13:11

another sheet of the book, for example, if we

00:13:13

needed to calculate the

00:13:15

sum of all orders on a new sheet, we would have to

00:13:17

select the entire corresponding range,

00:13:22

as a result, the form either included the

00:13:25

name of the sheet and the corresponding

00:13:27

range, but since the smart table

00:13:29

has a unique name, then limit of

00:13:32

the book, we can only refer to it,

00:13:34

so the name is to enter the function we need

00:13:37

and then we begin to enter the name of the

00:13:39

table. Excel will automatically offer

00:13:42

options from those already available in the document; all that

00:13:45

remains is to substitute the ones needed for

00:13:47

this, press the tab key; however, we have

00:13:50

indicated the entire table in its entirety, but we are

00:13:53

only interested in a specific one column,

00:13:55

so let's open the square bracket and

00:13:57

Excel will again offer options: these are the

00:13:59

column headings of the previously specified

00:14:02

table, select the desired column and

00:14:05

ultimately get the result without

00:14:06

going to another sheet of the book and

00:14:08

highlighting whatever it is. Well, it’s worth

00:14:13

mentioning that smart tables allow you to

00:14:15

avoid problems when working with

00:14:17

viewing functions like the vref function, since when

00:14:20

creating functions we will not refer

00:14:22

to some range, but a specific

00:14:25

column, smart tables,

00:14:26

firstly, there is no need to

00:14:29

fix the range since the smart table

00:14:31

has a different coordinate system, and

00:14:33

secondly, there is no need to

00:14:35

change range formula when

00:14:38

new data is entered into the table, the smart

00:14:41

table will pick it up automatically.

00:14:48

Of course, there are situations when smart

00:14:50

tables can be local, for example,

00:14:53

when working with forms that have a

00:14:55

certain shape or tables that

00:14:57

have multi-line headers with

00:14:59

merged cells,

00:15:01

however, if we are talking about an array of data

00:15:03

that is needed analyze using

00:15:05

pivot table chart formulas or any

00:15:07

other Excel tools,

00:15:08

volume tables will be an excellent solution and will

00:15:11

significantly speed up and simplify your

00:15:13

work, but if in the process you decide that a

00:15:15

smart table is no longer suitable for you, then

00:15:20

you can convert it to a regular range using the appropriate

00:15:21

tool with tabs designer

00:15:23

the table will be transformed and we will get

00:15:26

the most common data on a sheet

00:15:28

spreadsheets rate this video

00:15:30

if you find it useful

00:15:32

subscribe to the channel and see you in

00:15:34

new videos

Description:

👉Курс по Excel - https://andreysukhov.ru// Умная таблица в Excel появилась уже давно, однако многие пользователи их по-прежнему не применяют, предпочитая оформлять данные с помощью других стандартных инструментов форматирования программы. Тем не менее такие таблицы неспроста получили название «умные», ведь с их помощью можно не только максимально быстро оформить диапазон с данными, применив к нему какой-то из стандартных или пользовательских стилей, но они также позволяют максимально упростить и ускорить вычисления. Это видео является простой инструкцией, которая позволит вам повысить эффективность работы в Excel с помощью умных таблиц. *СОДЕРЖАНИЕ* 00:00 | Введение - преимущества умных таблиц 00:56 | Как сделать таблицу в Excel 01:54 | Форматирование таблицы в Excel (Стили) 03:11 | Имя (название) умной таблицы 04:09 | Сортировка, фильтр и срез в умных таблицах 06:20 | Строка итогов 07:16 | Как добавить строку или столбец в умную таблицу 08:26 | Как в таблице перенести строку или столбец 09:14 | Как удалить дубликаты в умной таблице 10:23 | Сводная таблица в Excel 11:40 | Умные таблицы и диаграммы 12:20 | Особенности проведения расчетов в умных таблицах 15:14 | Как удалить умную таблицу ★ Авторские книги и курсы: https://andreysukhov.ru ★ Скачать курс по Excel: https://andreysukhov.ru/education/excel-free/ ★ Телеграм: https://t.me/excelmate ★ Дзен: https://zen.yandex.ru/id/5c4d8a530aaeef00ac928ac3 ★ Группа ВК: https://vk.com/public212583018 #Excel

Preparing download options

Popular

HD video

Only sound

All

* — If the video is playing in a new tab, go to it, then right-click on the video and select "Save video as..."

** — Link intended for online playback in specialized players

Questions about downloading video

How can I download "Умная таблица в Excel ➤ Секреты эффективной работы" video?

http://unidownloader.com/ website is the best way to download a video or a separate audio track if you want to do without installing programs and extensions.
The UDL Helper extension is a convenient button that is seamlessly integrated into YouTube, Instagram and OK.ru sites for fast content download.
UDL Client program (for Windows) is the most powerful solution that supports more than 900 websites, social networks and video hosting sites, as well as any video quality that is available in the source.
UDL Lite is a really convenient way to access a website from your mobile device. With its help, you can easily download videos directly to your smartphone.

Which format of "Умная таблица в Excel ➤ Секреты эффективной работы" video should I choose?

The best quality formats are FullHD (1080p), 2K (1440p), 4K (2160p) and 8K (4320p). The higher the resolution of your screen, the higher the video quality should be. However, there are other factors to consider: download speed, amount of free space, and device performance during playback.

Why does my computer freeze when loading a "Умная таблица в Excel ➤ Секреты эффективной работы" video?

The browser/computer should not freeze completely! If this happens, please report it with a link to the video. Sometimes videos cannot be downloaded directly in a suitable format, so we have added the ability to convert the file to the desired format. In some cases, this process may actively use computer resources.

How can I download "Умная таблица в Excel ➤ Секреты эффективной работы" video to my phone?

You can download a video to your smartphone using the website or the PWA application UDL Lite. It is also possible to send a download link via QR code using the UDL Helper extension.

How can I download an audio track (music) to MP3 "Умная таблица в Excel ➤ Секреты эффективной работы"?

The most convenient way is to use the UDL Client program, which supports converting video to MP3 format. In some cases, MP3 can also be downloaded through the UDL Helper extension.

How can I save a frame from a video "Умная таблица в Excel ➤ Секреты эффективной работы"?

This feature is available in the UDL Helper extension. Make sure that "Show the video snapshot button" is checked in the settings. A camera icon should appear in the lower right corner of the player to the left of the "Settings" icon. When you click on it, the current frame from the video will be saved to your computer in JPEG format.

What's the price of all this stuff?

It costs nothing. Our services are absolutely free for all users. There are no PRO subscriptions, no restrictions on the number or maximum length of downloaded videos.