Окончательная компиляция советов и рекомендаций 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
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.
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 на работе, в школе и дома.
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
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
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.