Окончательная компиляция советов и рекомендаций Excel для повышения вашей производительности с помощью Excel. Обновлено на 2020 год! Я объединил давние советы и приемы с новыми, добавленными Microsoft в 2019 и 2020 годах. Вы найдете решения наиболее распространенных проблем, с которыми вы сталкиваетесь в Excel.

содержание
1. Переместить данные 0:35
2. XLOOKUP () 1:04
3. Список фильтров 2:24
4. Идеи 3:04
5. Удалить пробелы 3:54
6. Инструмент быстрого анализа 4:28
7. AutoFit Ширина столбца 5:27
8. Абсолютная ссылка на ячейку 5:57
9. Вставьте специальные значения 7:19
10. Раскрывающийся список 8:05
11. ФИЛЬТР () 9:15
12. Удалить дубликаты 10:43
13. Разница между списками 11:31
14. Flash Fill 12:15
15. Автозаполнение 13:06
16. Типы данных 13:53
17. Транспонировать 15:11
18. Стоп-кадр 15: 37
19. Текст в столбцы 16:27
20. Рекомендуемая сводная таблица 17:25
21. Слайсеры 18:04
22. Условное форматирование 19:01
23. ЕСЛИ () 20:29
24. 3D Ссылки 21:12
25. Прогнозный лист 22:45
26. САУМИФЫ () 24:00
27. ПОЗВОНОК () 25:15
28. Ctrl-клавиши со стрелками 26:03
29. Заполненные карты 26:26
30. PMT () 27:13
31. Показать формулы 28:20
32. Расширенный выбор 29:10
33. Названный ярлык диапазона 29:56
34. Спрятать клетки 30:47
35. COUNTBLANK () 31:30
36. Запрос на естественный язык 31:59
37. Поиск цели 33:08
38. Вставить скриншот 34:30
39. Power Pivot 35:14
40. 3D Карты 36:16
41. ISBLANK () 39:08
42. Анализ ToolPak 39:45
43. КОНВЕРТ () 40:48
44. Получить данные из Интернета 41:24
45. Люди График 42:59
46. ​​СОРТ () 44:18
47. Информация о строке состояния 45:36
48. Вставить несколько строк 46:22
49. ВЫБРАТЬ () 46:53
50. УНИКАЛЬНО () 48:50

Follow-along with the Advanced Pivot Tables tutorial and download the accompanying Excel exercise files here ► www.simonsezit.com/adv-pivottables-course-exercise-files/

The PivotTable function is one of the most impressive and powerful functions that Excel offers. With a few clicks, you can perform data analysis that would otherwise take forever. In this 2.5 hour pivot tables tutorial, weve assumed youve used a pivot table before and are looking to build on this skill.

We start with a quick pivot tables recap before moving onto combining data from multiple worksheets for a pivot table, combining and grouping data, different pivot table layouts and styles and so much more.

This pivot tables Excel 2019 course is aimed at intermediate to advanced users. Existing knowledge of pivot tables and Excel is advised.

This video is taken from the full, Simon Sez IT, Advanced PivotTables in Excel course, available here: www.simonsezit.com/courses/microsoft/advanced-pivottables-in-excel-training-course/

You can also download the practice exercises for free and follow along here ► www.simonsezit.com/adv-pivottables-course-exercise-files/

Advanced Pivot Table Course Markers:

0:00:00 — Introduction to Advanced PivotTables
0:04:02 — PivotTables Recap
0:16:45 — Importing data from a text file
0:26:43 — Importing data from Access
0:35:18 — Exercise 01
0:40:03 — Cleaning Data
0:57:42 — Tabular Data
1:14:04 — Exercise 02
1:17:21 — Creating and Manipulating a PivotTable
1:29:48 — Combining Data from Multiple Worksheets
1:41:52 — Grouping and Ungrouping
1:52:52 — Report Layouts
2:03:48 — Formatting Error Values and Empty Cells
2:12:13 — Exercise 03
2:13:49 — PivotTable Styles
2:25:28 — Custom Number Formatting
2:41:54 — Exercise 04

To see more, free Advanced Excel tutorials, take a look at this 4.5 hour YouTube course: youtu.be/gjfjn8Lwa28

Other, free Microsoft 2019 playlists from Simon Sez IT:

Microsoft Project 2019 course playlist: www.youtube.com/playlist?list=PLzj7TwUeMQ3jo_PtA6h6LNhvMbWNtJsXL

Microsoft Excel 2019 Beginners playlist:
www.youtube.com/playlist?list=PLzj7TwUeMQ3gABy6lbJgp6kZtXVhadATg

Microsoft Excel 2019 Advanced playlist:
www.youtube.com/playlist?list=PLzj7TwUeMQ3gL_KnxdZzkDExVeX5vngx9

Stay in touch!
SimonSezIT.com: www.SimonSezIT.com/
StreamSkill.com: StreamSkill.com/
YouTube Channel: www.youtube.com/user/simonsezittraining

Subscribe to our channel:
www.youtube.com/simonsezittraining?sub_confirmation=1

If you enjoyed the video, please give a «thumbs up» and subscribe
to the channel ;-)

Access Expert Tutorial

For certificates, exams, and badges, join our Patreon community: www.patreon.com/learnittraining

Join our online community! — www.offsitebylearnit.com

Exercise Files: bit.ly/3jcpg3o

Manuals: bit.ly/30xZb59
Username: manuals
Password: password

Introduction 0:03
Turning Your Database Folder Into a Trusted Location 1:25
Filtering by Form 17:51
Columnar Form Views — Part 1 30:30
Columnar Form Views — Part 2 39:52
Tabular Forms 44:59
Split Forms 57:30
Justified Forms — Part 1 1:06:19
Justified Forms — Part 2 1:15:07
Form Sections 1:19:20
Adding Graphic Elements 1:37:42
Hand-Built Forms 1:46:37
Creating Forms from the Query 2:05:03
Building Forms from the Query 2:26:30
Tracing Dependencies from Form to Query to Table 2:39:55
Multi-Page Tabbed Forms 2:49:25
Parent and Child Forms — Method 1 3:08:47
Parent and Child Forms — Method 2 3:28:53
Parent and Child Forms — Method 3 3:33:07
Form Wizards Datasheet Layout 3:38:44
Wrapping Up 3:44:26
Introduction 3:45:26
Viewing Reports — Part 1 3:46:39
Viewing Reports — Part 2 3:58:12
Views Available for Reports 4:02:56
Modifying Your First Report 4:13:48
The Form Wizard 4:35:27
Sorting in Your Report 4:51:17
Grouping in Your Report — Part 1 5:03:22
Grouping in Your Report — Part 2 5:20:38
Creating Summary Reports via the Wizard 5:22:03
Modifying Summary Reports 5:38:06
Special Case of CAN GROW 6:00:41
Adding a ROLODEX Effect 6:06:48
Mailing Labels 6:18:32

Лучшие 25 советов и рекомендаций для Excel 2016. Используйте эти советы и рекомендации для повышения эффективности с помощью Excel. Я уверен, что вы обнаружите некоторые, которые вы не видели раньше, и другие, которые вы использовали неправильно. Мой личный фаворит # 21. Станьте более продуктивными, используя Excel на работе, в школе и дома.

Посмотрите видео Advanced Excel по адресу youtu.be/PU8ACyYxJBk.
Еще 15 советов и рекомендаций по Excel 2016: youtu.be/L9SKTj2gevA
Введение в Excel для начинающих: youtu.be/WxUQLHJn77Q

Содержание:
1. Инструмент быстрого анализа 0:47
2. Фильтр 1:44
3. Раскрывающийся список 2:25
4. AutoFit Ширина столбца 3:12
5. Транспонировать 3:47
6. Удалить дубликаты 4:13
7. Поиск цели 5:23
8. VLOOKUP 6:55
9. Вспышка и автозаполнение 9:06
10. Вставьте специальные значения 10:47
11. Изображения в чартах 11:50
12. ЕСЛИ Функция 12:28
13. Вставьте скриншот 13:40
14. Абсолютная ссылка на ячейку 14:15
15. Показать формулы 15:54
16. Текст в столбцы 16:57
17. Условное форматирование 18:11
18. PowerPivot 19:36
19. Стоп-кадр 20:46
20. Ctrl-клавиши со стрелками 21:40
21. 3D Ссылки 21:58
22. Прогнозный лист 23:43
23. Функция SUMIFS 24:16
24. Функция IFERROR 25:38
25. Заполненные карты 26:30

Direct links to file:
START FILE: people.highline.edu/mgirvin/YouTubeExcelIsFun/E W T M Accounting Excel Seminar-StartFileSoYouCanFollowAlong.xlsx
FINISHED FILE: people.highline.edu/mgirvin/YouTubeExcelIsFun/E W T M Accounting Excel SeminarAllFinishedExamplesAfterVideoDone.xlsm

Keyboards 0:01:47
Jump: Ctrl Arrow 0:02:20
Go To Cell A1: Ctrl Home 0:02:36
Format Cells dialog box or in a chart Format Chart dialog box: Ctrl 1 0:03:40
Currency Number Format: Ctrl Shift 4 0:04:08
Highlight column: Ctrl Shift Arrow0:04:20
Currency Vs Accounting Number Format 0:05:00
Alt keys: 0:05:47
PivotTable: Alt, N, V, T 0:05:47
PivotTable 2003: Alt, D, P 0:05:47
Page Setup: Alt, P, S, P 0:05:47
SUM: Alt = 0:08:38
«Put thing in cell and move selected cell up»: Shift Enter 0:09:35
«Put thing in cell and keep cell selected»: Ctrl Enter 0:09:35
Select sheet to right: Ctrl PageDown 0:11:11
Select sheet to left: Ctrl PageUp 0:11:11
Number Formatting As Façade: 0:11:52
Decimal Number Format 0:12:38
Date Number Format 0:13:56
Keyboard for todays hard caded date: 0:13:56
Time Number Format 0:17:10
Percentage Number Format 0:21:25
Efficient Formula Creation 0:25:01
Excels Golden Rule: If a formula input can vary, put it in a cell and refer to it in the formula with a cell reference 0:25:01
Formula elements, types of formulas, types of data 0:25:01
Monthly Allocation Formula: illustrate formula input that can be hard coded into formula 0:27:00
Tax (inefficient formula): illustrate formula input that can should NOT hard coded into formula 0:27:35
Tax (efficient formula): illustrate Golden Rule 0:27:35
Net Cash In formula: illustrate Golden Rule 0:27:35
Net Income formula: illustrate formula with built-in function within a larger formula 0:30:00
In Balance? formula: illustrate Logical formula 0:30:40
First

Access Beginner Tutorial

For certificates, exams, and badges, join our Patreon community: www.patreon.com/learnittraining

Join our online community! — www.offsitebylearnit.com

Exercise Files: bit.ly/3jcpg3o

Manuals: bit.ly/30xZb59
Username: manuals
Password: password

Opening a Sample Database 0:03
The Database Objects 8:57
The Interface 14:51
Introducing Tables 20:12
Simple Data Entry 27:48
Introducing Queries 31:01
Practicing Data Entry in Queries 40:01
Introducing Forms 41:06
Data Entry Into Forms 46:49
Introducing Reports 47:35
Sorting to Create Groups 53:33
Finding Data in Tables 1:04:28
Filtering 1:10:30
Adding Total Rows 1:20:15
Starting a New Database 1:24:14
Creating Tables in Design View 1:33:02
Entering Your First Record 1:47:22
Using Quick Starts to Create Tables 1:51:12
Entering Data Into Your Second Table 2:10:25
Summary 2:19:34
Introduction 2:22:28
Looking at the First Draft of a Database 2:23:29
Creating Relationships with Lookup Lists 2:29:41
The Relationships Window 2:33:33
Relating by Dragging Field Names 2:36:52
Dragging to Create 1-to-1 Relationships 2:57:30
Changing Dragged Relationships to Lookup Relationships 3:00:30
Limit-To-List Properties 3:09:43
Enforced Referential Integrity 3:15:12
Seeing the Referential Integrity Being Enforced 3:22:41
Cascade Edits and Deletions 3:26:52
More Field Properties 3:33:55

Overview:

Even when times are good, it is necessary to know how your business is doing financially. In the current economic climate that businesses are facing, it is even more important to be able to see how these challenges may affect your business and how to react to them.

In this session, we will reflect and explore the following questions to ask yourself as a key member of your company:

Are we as profitable as we can be?

What areas of our income statement should we focus on?

How do we know if there is a problem in collecting amounts owed from customers?

How do we measure if our inventory is being managed effectively?

What is our break-even point ( i.e., what level of sales do we need to cover our costs and expenses?

We will then explore how to go about making informed decisions and recommendations based on your answers to these questions.

Learn the top 30 advanced Microsoft Excel features you need to know to go from beginner to advanced Excel user.

Table of Contents:

1. Get Data from Folders 0:22
2. Text to Columns 5:34
3. Remove Duplicate Values 7:22
4. AutoFill

Excel VBA Beginner Tutorial

For certificates, exams, and badges, join our Patreon community: www.patreon.com/learnittraining

Join our online community! — www.offsitebylearnit.com

Excercise Files: bit.ly/3kVR38Y

Manuals: bit.ly/30xZb59
Username: manuals
Password: password

Introduction 0:03
What is VBA? 1:44
Object Oriented Programming Languages 5:59
Turning on the Developer Tab 10:17
The Macro Recorder 13:40
Using Relative References 20:39
Recording Simple Macros 27:51
Multi-Step Macro Recording 39:25
Sort and Filter Macro Recording 45:26
Protecting and Formatting Sheets with the Macro Recorder 51:35
VBA Interface Setup 55:09
Recorder Code vs. Manual Code 1:01:11
Introduction to Editing Macros 1:12:28
Debugging Macros 1:16:53
Grammar in VBA 1:28:00
Macro Scripting Basics 1:33:04
Range 1:40:41
Selection

Download file used in the video with step by step instructions and links to more tutorials: www.myonlinetraininghub.com/workbook-downloads

In this video you will learn how to create an interactive Excel dashboard from scratch using the built in Excel tools. No add-ins or VBA/Macros. Just plain Excel. Applies to Excel 2007 onward for Windows