100 секретных советов и рекомендаций по Excel

1. Добавьте серийные номера

Если вы работаете с большими данными, лучше всего добавить к ним столбец с серийным номером. Для меня лучший способ сделать это — применить массив (Control + T) к данным, а затем добавить 1 к серийному номеру выше, как показано ниже.

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

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

2. Вставьте текущую дату и время.

Лучший способ вставить текущую дату и время — использовать функцию СЕЙЧАС, которая принимает системные дату и время и возвращает их.

Единственная проблема с этой функцией заключается в том, что она нестабильна и каждый раз, когда вы что-то пересчитываете, она обновляет свое значение. А если вы не хотите этого делать, лучший способ — преобразовать его в твердое значение . Вы также можете использовать код VBA ниже.

 Sub timestamp() Dim ts As Date With Selection .Value = Now .NumberFormat = "m/d/yyyy h:mm:ss AM/PM" End With End Sub

Или эти методы вставки временной метки в ячейку .

3. Выберите несплошные ячейки

Обычно мы все делаем так: зажимаем клавишу Control и выделяем ячейки одну за другой. Но я обнаружил, что для этого есть гораздо лучший способ. Все, что вам нужно сделать, это выбрать первую ячейку и затем нажать SHIFT+F8.

Это позволяет вам добавлять или удалять режим выделения, в котором вы можете выбирать ячейки, просто выбирая их.

4. Кнопки сортировки

Если вы имеете дело с данными, которые необходимо часто сортировать, лучше всего добавить кнопку на панель быстрого доступа (если ее там еще нет).

Все, что вам нужно сделать, это нажать стрелку вниз на панели быстрого доступа, затем выбрать «Сортировать по возрастанию» и «Сортировать по убыванию». Он добавляет две кнопки в QAT.

5. Переместить данные

Я уверен, что вы думаете о копировании и вставке, но вы также можете использовать для этого перетаскивание.

Просто выберите диапазон, в котором есть данные, затем щелкните границу выделения. Удерживая его, переместите туда, куда нужно его положить.

6. Строка состояния

Строка состояния все еще здесь, но мы почти не используем ее в полной мере. Если щелкнуть по нему правой кнопкой мыши, вы увидите, что можно добавить множество опций.

7. Буфер обмена

Существует проблема с обычным копированием и вставкой : вы можете использовать только одно значение за раз.

Но вот в чем интерес: когда вы копируете значение, оно попадает в буфер обмена, и если вы откроете буфер обмена, вы сможете вставить все скопированные значения. Чтобы открыть буфер обмена, нажмите «Перейти на вкладку «Главная»» ➜ «Редактирование», затем нажмите стрелку вниз.

Это откроет буфер обмена слева, и вы сможете вставить туда значения.

8. Пули

Самый простой способ вставить маркеры в Excel — использовать собственное форматирование. Вот шаги, которые необходимо выполнить:

1. Нажмите Ctrl+1 , и вы получите диалоговое окно «Формат ячейки».
2. На вкладке «Номер» выберите «Пользовательский».
3. В строке ввода введите следующее форматирование.
   1. ● Общие; ● Общие; ● Общие; ● Общие.
4. В конце нажмите ОК.

Теперь каждый раз, когда вы вводите значение в ячейку, Excel добавляет перед ним маркер.

9. Копия таблицы.

Чтобы создать копию листа в той же книге, лучше всего перетащить ее.

Просто нажмите и удерживайте вкладку имени листа, затем перетащите ее влево или вправо туда, где вы хотите сделать копию.

10. Отменить-Повторить

Как и в случае с кнопками сортировки, в QAT можно добавить кнопки «Отменить» и «Повторить». Самое приятное в этих кнопках то, что вы можете использовать их для отмены определенного действия, не нажимая горячую клавишу снова и снова.

11. Автоформат

Если вы имеете дело с финансовыми данными , автоматическое форматирование может стать одним из лучших инструментов. Он просто применяет формат к маленьким и большим наборам данных (особенно, когда данные представлены в табличной форме).

1. Во-первых, вам нужно добавить его на панель быстрого доступа ( вот шаги ).
2. После этого, когда вам понадобится применить формат, просто выберите данные, к которым вы хотите его применить, и нажмите кнопку АВТО ФОРМАТ на панели инструментов быстрого доступа.
3. Откроется окно для выбора типа форматирования и после его выбора нажмите «ОК».

АВТОФОРМАТ представляет собой комбинацию шести различных форматов, и у вас есть возможность отключить любое из них при его применении.

12. Формат художника

Простая идея Format Painter — скопировать и вставить форматирование из одного раздела в другой. Допустим, у вас есть определенное форматирование (стиль и цвет шрифта, цвет фона в ячейке , жирный шрифт, граница и т. д.) в диапазоне B2:D7, и с помощью средства форматирования вы можете скопировать это форматирование в диапазоне B9:D14 с помощью одного щелкните.

1. Сначала выберите диапазон B2:D7.
2. После этого перейдите на вкладку «Главная» ➜ «Буфер обмена», затем нажмите «Формат Painter».
3. Теперь выберите ячейку C1, и она автоматически применит форматирование к B9: D14.

Format Painter работает быстро и позволяет легко применять формат из одного раздела в другой.

Связанный: Ярлык Format Painter

13. Сотовое сообщение

Допустим, вам нужно добавить в ячейку определенное сообщение, например «Не удалять значение», «Введите свое имя» или что-то в этом роде.

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

1. Сначала выберите ячейку, в которую вы хотите добавить сообщение.
2. После этого перейдите на вкладку «Данные» ➜ «Инструменты с данными» ➜ «Проверка данных» ➜ «Проверка данных».
3. В окне «Проверка данных» перейдите на вкладку «Входное сообщение».
4. Введите заголовок и сообщение и обязательно установите флажок «Показывать входное сообщение при выборе ячейки».
5. В конце нажмите ОК.

Как только сообщение отобразится, вы можете перетащить его, чтобы изменить его положение.

14. Зачеркнуто

В отличие от Word, в Excel на ленте нет возможности применить зачеркивание . Но я выяснил, что есть 5 способов сделать это и самый простой из них — сочетание клавиш.

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

Ctrl + 5

И если вы используете MAC, то:

⌘ + ⇧ +

Краткое примечание: вы можете использовать те же сочетания клавиш, если вам нужно сделать это для части текста.

15. Добавьте штрих-код

Это один из тех секретных советов, которые большинство пользователей Excel игнорируют. Чтобы создать штрих-код в Excel, вам просто нужно установить этот шрифт штрих-кода от ID-AUTOMATIC.

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

▷ узнать больше об этом совете

16. Название месяца

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

• Сначала выделите ячейку с датой и откройте параметры форматирования (используйте Ctrl+1).
• Выберите опцию «Пользовательский» и добавьте «МММ» или «МММММ» для месяца или «ГГГГ» для формата года.
• В конце нажмите ОК.

Пользовательское форматирование просто меняет формат ячейки с даты на год/месяц, но значение остается прежним.

17. Выделить пустые ячейки

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

• Сначала выделите все данные на листе, используя сочетание клавиш Ctrl + A.

• После этого перейдите на вкладку «Главная» ➜ «Редактировать» ➜ «Найти и выбрать» ➜ «Перейти в раздел «Специальные».

• В диалоговом окне «Перейти к специальному» выберите «Пусто» и нажмите «ОК».

• На этом этапе вы выбрали все пустые ячейки и теперь применяете цвет ячейки, используя настройки шрифта.

…но вы также можете использовать для этого условное форматирование

18. Цвет шрифта с пользовательским форматированием

В Excel мы можем применять собственное форматирование, а в пользовательском форматировании есть возможность использовать цвета шрифта (ограниченно, но полезно).

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

 [Green]#,###;[Red]-#,###;0; 

1. Сначала выберите ячейки, к которым вы хотите применить этот формат.
2. После этого откройте параметр формата с помощью сочетания клавиш Ctrl + 1 и перейдите в категорию «Пользовательский» и «Пользовательский формат» в диалоговом окне ввода.
3. В конце нажмите ОК.

19. Цвет темы

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

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

1. Перейдите на вкладку «Макет» ➜ «Темы» ➜ «Сохранить текущую тему». Он открывает диалоговое окно « Сохранить как », дает название вашей теме и сохраняет ее.
2. И теперь каждый раз вам понадобится всего один щелчок, чтобы изменить любой стиль листа на свой собственный.

20. Очистить форматирование

Это простое сочетание клавиш, которое можно использовать для очистки форматирования ячейки или диапазона ячеек.

Alt ➜ Ч ➜ Е ➜ F

В противном случае вы также можете использовать опцию «Очистить форматирование» на вкладке «Главная» (вкладка «Главная» ➜ «Правка» ➜ «Очистить» ➜ «Форматы»).

21. Штрафные дела

В Excel у нас есть три разные функции (НИЖНЯЯ, ВЕРХНЯЯ и ПРАВИЛЬНАЯ) для преобразования текста в разные регистры. Но нет возможности преобразовать текст в регистр предложений. Вот формула, которую вы можете использовать:

=ВЕРХНИЙ(ЛЕВЫЙ(A1,1))&НИЖНИЙ(ПРАВЫЙ(A1,LEN(A1)-1))

Эта формула преобразует первую букву предложения в верхний регистр, а остальные буквы в нижний ( узнайте, как работает эта формула ).

22. Случайные числа

В Excel есть две специальные функции, которые можно использовать для генерации случайных чисел. Первый — RAND, который генерирует случайные числа от 0 до 1.

А второй — RANDBETWEEN, который генерирует случайные числа в диапазоне двух конкретных чисел.

ВНИМАНИЕ: эти функции нестабильны, поэтому каждый раз, когда вы пересчитываете таблицу или нажимаете Enter, они обновляют свои значения, поэтому используйте их с осторожностью. Вы также можете использовать RANDBETWEEN для генерации случайных букв и случайных дат .

23. Считаем слова

В Excel нет специальной функции для подсчета слов. С помощью LEN можно считать символы, но не слова. Но вы можете использовать следующую формулу, которая поможет вам подсчитать слова в ячейке .

=LEN(A1)-LEN(ЗАСТАВИТЬ(A1», «»)))+1

Эта формула подсчитывает количество пробелов в ячейке и добавляет 1 после того, что равно общему количеству слов в ячейке.

24. Вычислить возраст

Лучший способ рассчитать возраст человека — использовать DATEDIF . Эта загадочная функция специально разработана для определения разницы между диапазоном дат .

И формула будет такая:

=»Ваш возраст: «& DATEDIF(Дата рождения,Сегодня()»,y») &» Год(ы), «& DATEDIF(Дата рождения,СЕГОДНЯ()»,ym»)& ” МЕСЯЦ(ы) & «& DATEDIF(Дата рождения,СЕГОДНЯ()»,md»)& «День(а)».

25. Рассчитайте соотношение

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

=Большое число/Меньшее число&”:”&”1″

Эта формула делит большее число на меньшее, поэтому за основу можно взять меньшее число (1).

26. Корень числа

Чтобы вычислить квадратный корень, кубический корень или любой корень числа, лучше всего использовать формулу экспоненты. В формуле показателя степени вы можете указать N-е число, для которого хотите вычислить корень.

=число^(1/n)

Например, если вы хотите вычислить квадратный корень из 625, формула будет такой:

=625^(1/2)

27. Дни в месяце

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

=ДЕНЬ(ЕМЕСЯЦ(СЕГОДНЯ(),0))

28. Последняя встреча месяца.

Чтобы просто получить последнюю дату месяца, вы можете использовать следующую динамическую формулу.

=ДАТА(ГОД(СЕГОДНЯ()),МЕСЯЦ(СЕГОДНЯ())+1,0)

29. Обратный ВПР

Как мы все знаем, невозможно найти значение с помощью ВПР. Но если вы переключитесь на INDEX MATCH, вы сможете смотреть в любом направлении.

30. СУММПРОИЗВИТ ЕСЛИ

Вы можете использовать приведенную ниже формулу для создания условного СУММПРОИЗВ и значений произведения с использованием условия.

= СУММПРОИЗВ (–(C7:C19=C2),E7:E19,F7:F19)

31. Плавная линия

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

1. Выберите строку данных на диаграмме и щелкните ее правой кнопкой мыши.
2. Выберите «Форматировать ряд данных».
3. Перейдите в «Заливка и линия» ➜ «Линия» ➜ установите флажок «Гладкая линия».

32. Форматирование диаграмм

Скопировать формат диаграммы в Excel

33. Скрыть метки осей

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

1. Выберите горизонтальную/вертикальную ось на диаграмме.
2. Перейдите к меткам «Формат оси».
3. В позиции метки выберите «Нет».

И снова, если вы хотите это показать, просто выберите «Рядом с осью».

34. Дисплеи

Если вы имеете дело с большими числами на диаграмме, вы можете изменить единицы измерения значений оси.

1. Выберите ось диаграммы и откройте параметры формата «Формат оси».
2. В параметрах оси перейдите к «Показать единицы», где вы можете выбрать единицу измерения для значений оси.

35. Круглый угол

Я часто использую диаграммы Excel с закругленными углами, и если вам тоже нравится использовать закругленные углы, вот простые шаги.

1. Выберите диаграмму и откройте параметры форматирования.
2. Перейдите в «Заливка и линия» ➜ «Границы».
3. В участках границ проверьте закругленные углы.

36. Скрыть разрыв

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

• Покажите место для пустой ячейки.
• Используйте ноль.
• Соедините точки данных линией.

Вот шаги по использованию этих опций.

1. Щелкните правой кнопкой мыши диаграмму и выберите «Выбрать данные».
2. В окне выбора данных нажмите «Скрытая и пустая ячейка».
3. Выберите нужную опцию в «Показать пустую ячейку как».

Обязательно используйте «Соединить точки данных линией» (рекомендуется).

37. Пиктограмма в Excel

Как создать пиктограмму в Excel

38. Шаблон диаграммы

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

• Закончив с предпочтительным форматированием, щелкните его правой кнопкой мыши и выберите «Сохранить как шаблон».

• Используя диалоговое окно «Сохранить как», сохраните его в папке шаблона.
• Чтобы вставить новый шаблон с вашим любимым шаблоном, выберите его из шаблонов в диалоговом окне «Вставить диаграмму».

39. Диаграмма по умолчанию

40. Скрытые клетки

Когда вы скрываете ячейку из диапазона данных диаграммы, эта точка данных также скрывается из диаграммы. Чтобы решить эту проблему, просто выполните следующие действия.

1. Выберите диаграмму и щелкните ее правой кнопкой мыши.
2. Перейдите в ➜ «Выбрать данные» ➜ «Скрытые и пустые ячейки».
3. Во всплывающем окне установите флажок «Показывать данные в скрытых строках и столбцах».

41. Печатные названия

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

• Перейдите на вкладку «Макет» ➜ «Параметры страницы» ➜ нажмите «Печать заголовков».
• Теперь в окне настройки страницы перейдите на вкладку листа и укажите следующее.

1. Область печати : выберите все данные, которые хотите распечатать.
2. Строки для повторения сверху: строки заголовка, которые вы хотите повторять на каждой странице.
3. Столбцы для повтора слева: столбцы, которые вы хотите повторить слева на каждой странице (если применимо).

42. Порядок страниц

Указание порядка страниц очень полезно, если вы хотите распечатать большие данные.

• Перейдите на вкладку «Файл» ➜ «Печать» ➜ «Настройка печати» ➜ вкладка «Листы».
• Теперь у вас есть два варианта:
  • Первый вариант: печатать страницы в вертикальном порядке.
  • Второй вариант: печатать страницы в горизонтальном порядке.

43. Печать комментариев

Если вы добавляете комментарии к своим отчетам, вы также можете их распечатать. В конце всех распечатанных страниц вы можете получить список всех комментариев.

• Перейдите на вкладку «Файл» ➜ «Печать» ➜ «Настройка печати» ➜ вкладка «Листы».
• В разделе «Печать» выберите «В конце листа» в раскрывающемся списке «Комментарии».
• Нажмите ОК.

44. Масштабируйте по размеру

Иногда нам сложно распечатать все данные на одной странице. В этой ситуации вы можете использовать опцию «Масштабировать по размеру», чтобы разместить все данные на одной странице.

1. Перейдите на вкладку «Файл» ➜ «Печать» ➜ «Настройка печати» ➜ вкладка «Страница».
2. Далее вам необходимо настроить два параметра:
   1. Отрегулируйте % от нормального размера.
   2. Укажите количество страниц, на которых вы хотите разместить все ваши данные, используя ширину и длину.

45. Пользовательский верхний колонтитул

Вместо использования номера страницы в верхнем и нижнем колонтитуле вы также можете использовать собственный верхний и нижний колонтитул.

• Перейдите на вкладку «Файл» ➜ «Печать» ➜ «Настройка печати» ➜ «Верхний/нижний колонтитул».
• Нажмите кнопку пользовательского верхнего или нижнего колонтитула.

• Здесь вы можете выбрать выравнивание верхнего и нижнего колонтитула.

• И можно использовать следующие варианты:
  1. Номер страницы
  2. Номер страницы с общим количеством страниц.
  3. Дата
  4. Время
  5. Путь к файлу
  6. Имя файла
  7. Имя листа
  8. Картина

46. Центр на странице

Представьте, что на странице нужно распечатать меньше данных. В этом случае вы можете выровнять его по центру страницы при печати.

1. Перейдите на вкладку «Файл» ➜ «Печать» ➜ «Настройка печати» ➜ «Поля».
2. В «Центре на странице» у вас есть два варианта выбора.
   • Горизонтально: выравнивает данные по центру страницы.
   • Вертикально: выравнивает данные по середине страницы.

Прежде чем распечатать страницу, убедитесь, что вы видите изменения в предварительном просмотре .

47. Область печати

Самый простой способ распечатать диапазон — выбрать этот диапазон и использовать опцию «Печать выделения». Но что делать, если вам нужно часто печатать этот диапазон, в этом случае вы можете указать область печати и распечатать ее, не выбирая ее каждый раз.

Перейдите на вкладку «Макет», щелкните раскрывающееся меню «Область печати», а затем выберите параметр «Установить область печати».

48. Пользовательская маржа

• Перейдите на вкладку «Файл» ➜ «Печать».
• Как только вы нажмете «Печать», вы получите мгновенный предварительный просмотр распечатки.
• Теперь в правом нижнем углу окна нажмите кнопку «Показать поля».

Он покажет все примененные поля, и вы можете изменить их, просто перетаскивая.

49. Значения ошибок

Вы можете заменить все значения ошибок при печати определенным значением (три других значения можно использовать в качестве замены).

• Перейдите на вкладку «Файл» ➜ «Печать» ➜ «Настройка печати» ➜ «Лист».
• Выберите значение замены из раскрывающегося списка «Ошибка ячейки как».
• У вас есть три варианта использования в качестве замены.
  1. Белый
  2. Двойной знак минус.
  3. Ошибка «#Н/Д» для всех ошибок.

• После выбора значения замены нажмите кнопку ОК.

Я считаю, что использование «двойного знака минуса» — лучший способ отобразить ошибки в отчете при его печати на странице.

50. Пользовательский номер стартовой страницы.

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

• Перейдите на вкладку «Файл» ➜ «Печать» ➜ «Настройка печати» ➜ «Страница».
• В поле ввода «Номер первой страницы» введите номер, с которого вы хотите начать номер страницы.

• В конце нажмите ОК.

Эта опция будет работать только в том случае, если вы применили верхний/нижний колонтитул в своей электронной таблице.

51. Отслеживание важных ячеек

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

• Сначала перейдите на вкладку «Формула» ➜ «Аудит формулы» ➜ «Окно мониторинга».

• Теперь в диалоговом окне «Окно просмотра» нажмите «Добавить наблюдение».

• После этого выберите ячейку или диапазон ячеек, которые вы хотите добавить, и нажмите «ОК».

После того, как вы нажмете «ОК», вы получите конкретную информацию о ячейке (ячейках) в окне мониторинга.

52. Мгновенное заполнение

Flash-заливка — один из моих любимых вариантов использования в Excel. Это как подражатель: делай то, что сделал. Позволь мне привести пример.

Вот шаги по его использованию: у вас есть даты в диапазоне A1:A10, и теперь вы хотите получить месяц из дат в столбце B.

Все, что вам нужно сделать, это ввести месяц первой даты в ячейку B1, затем перейти к ячейке B2 и нажать сочетание клавиш CTRL + E. После этого месяц будет извлечен из остальных дат, как показано ниже. .

53. Объедините электронные таблицы

Я уверен, что где-то в прошлом вы получили от своего коллеги файл, в котором у вас есть 12 разных таблиц с данными за 12 месяцев. В этом случае лучшее решение — объединить все эти рабочие листы с помощью опции «Объединить» , и вот шаги, которые необходимо выполнить.

• Начните с добавления нового листа, затем перейдите на вкладку «Данные» ➜ «Инструменты с данными» ➜ «Консолидация».

• Теперь в окне «Объединить» щелкните стрелку вверху, чтобы добавить диапазон из первого листа, затем нажмите кнопку «Добавить».
• Затем вам нужно добавить ссылки на все рабочие листы, используя шаг выше.

• В конце нажмите ОК.

54. Защитите переплет

Добавить пароль в книгу довольно просто, вот шаги.

• При сохранении файла в диалоговом окне «Сохранить как» перейдите в раздел «Общие параметры инструментов».

• Добавьте пароль в «Пароль для открытия» и нажмите «ОК».

• Введите пароль еще раз и снова нажмите «ОК».
• В конце сохраните файл.

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

55. Живое изображение

В Excel использование живого изображения таблицы может помочь изменить ее размер в соответствии с пространством, а для создания живого изображения вы можете использовать его двумя разными способами.

Один из них — инструменты камеры, а второй — специальный вариант коллажа. Ниже приведены инструкции по использованию инструмента «Камера» , а для создания специального коллажа выполните действия, описанные ниже.

1. Выберите диапазон, который вы хотите вставить в качестве изображения, и скопируйте его.
2. Перейдите к ячейке и щелкните правой кнопкой мыши то место, куда вы хотите ее вставить.
3. Перейдите в раздел «Специальный коллаж» ➜ «Другой коллаж» ➜ «Параметры связанного изображения».

56. Форма пользователя

Некоторые пользователи Excel знают, что существует форма ввода данных по умолчанию, которую мы можем использовать. И самое приятное то, что для этого не нужно писать ни строчки кода.

Вот как его использовать:

1. Сначала убедитесь, что у вас есть таблица с заголовками, куда вы хотите ввести данные.
2. После этого выделите одну из ячеек в этой таблице и с помощью сочетания клавиш Alt + D + O + O откройте форму пользователя.

57. Пользовательская вкладка

У каждого из нас есть любимые варианты или варианты, которые мы часто используем. Чтобы получить доступ ко всем этим параметрам в одном месте, создайте вкладку и добавьте их.

1. Сначала перейдите на вкладку «Файл» ➜ «Параметры» ➜ «Настроить ленту».
2. Теперь нажмите «Новая вкладка» (это добавит новую вкладку).
3. После этого щелкните по нему правой кнопкой мыши и назовите его, затем назовите группу.
4. Наконец, нам нужно добавить параметры на вкладку, для этого перейдите в «Выбрать команды из» и добавьте их на вкладку одну за другой.
5. В конце нажмите ОК.

58. Поиск цели

Проще говоря, Goal Seek — это инструмент решения проблем. Это помогает вам найти входное значение, доказывая желаемое значение в результате.

… вот полное руководство, чтобы узнать больше .

59. Преобразование текста в речь

Это возможность Excel озвучивать текст, который вы вводите в ячейку или диапазон ячеек.

60. Именный пляж

Самый простой способ создать именованный диапазон — выбрать диапазон и создать его с помощью параметра «Создать из выделения». Вот шаги, которые необходимо выполнить:

• Выберите столбец/строку, для которой вы хотите создать именованный диапазон.
• Щелкните правой кнопкой мыши и выберите «Установить имя…».

• Выберите вариант добавления имени именованного диапазона и нажмите «ОК».

61. Гарнир

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

62. Удалите дубликаты

Одна из наиболее распространенных вещей, с которыми мы сталкиваемся при работе с большими данными, — это «дубликаты значений». В Excel удалить эти повторяющиеся значения довольно просто. Вот как это сделать.

1. Сначала выберите одну из ячеек данных или выберите все данные.
2. После этого перейдите в «Данные» ➜ «Инструменты с данными» ➜ «Удалить дубликаты».
3. На этом этапе у вас есть окно «Удалить дубликаты», и в этом окне выберите/отмените выбор столбцов, которые вы хотите учитывать/не учитывать при удалении повторяющихся значений.
4. В конце нажмите ОК.

После того, как вы нажмете «ОК», Excel удалит все строки выбранных данных, в которых значения являются повторяющимися, и отобразит сообщение с количеством удаленных значений и оставшимисяуникальными значениями .

63. Объединить текст (заполнить по ширине)

Допустим, у вас есть слова в диапазоне A1:A5, и вы хотите объединить их все в одну ячейку .

1. Во-первых, сделайте столбец А достаточно широким, чтобы весь текст можно было объединить в одну ячейку.
2. После этого выберите весь диапазон.
3. Теперь перейдите на вкладку «Главная» ➜ «Правка» ➜ «Заливка» ➜ «Выравнивание» .

64. Удалить определенный символ

В диапазоне A1:A5, и вы хотите объединить их все в одну ячейку. Вот как это сделать с выравниванием заполнения.

1. Все, что вам нужно сделать, это выбрать этот столбец и открыть диалоговое окно «Найти и заменить».
2. После этого нажмите на вкладку «Заменить».
3. Теперь в поле «Найти что» введите символ, который хотите заменить, и не забудьте оставить поле «Заменить на» пустым.
4. Теперь нажмите «Заменить все».

В тот момент, когда вы нажмете «Заменить все», Excel удалит этот конкретный символ из всего столбца.

Связанное: удаление первого символа из ячейки в Excel

65. Объединить текст

Итак, у вас есть текст в нескольких ячейках, и вы хотите объединить весь текст в одну ячейку. Нет, на этот раз не с наполнением и оправданием. Мы делаем это с помощью TEXT JOIN. Если вы используете Office 365, появилась новая функция TEXTJOIN, которая меняет правила игры, когда дело доходит до объединения текста.

Вот синтаксис:

TEXTJOIN(разделитель, ignore_empty, текст1, [текст2], …)

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

66. Неротированные данные

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

Но если вы преобразуете эту таблицу во что-то вроде приведенной ниже, вы легко сможете использовать ее где угодно. Итак, как вы это делаете?

Вот простые шаги, которым вам нужно следовать .

67. Удалить ячейки с ошибками

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

• Сначала перейдите на вкладку «Главная» ➜ «Правка» ➜ «Найти и заменить» ➜ «Перейти в раздел «Специальные».
• В диалоговом окне «Перейти к» выберите формулу и проверьте наличие ошибок.

• В конце нажмите ОК.

Как только вы нажмете «ОК», все ошибки будут выбраны, а затем вы сможете просто все удалить с помощью кнопки «Удалить».

68. Организация столбцов

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

Но у нас также есть готовый метод. В Excel вы можете сортировать столбцы так же, как сортируете строки, и, используя те же методы, вы можете расположить их в произвольном порядке.

⇢ Полная консультация

69. Преобразование в дату

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

Другие способы конвертировать текст в дату

70. От негатива к позитиву

Самый простой способ преобразовать отрицательное число в положительное — использовать метод .

71. Формат художника

Прежде чем я начал использовать Format Painter для применения форматирования ячеек, я использовал специальную вставку с горячей клавишей.

1. Выберите ячейку или диапазон, из которого вы хотите скопировать форматирование ячейки.
2. Перейдите на вкладку ➜ «Главная» ➜ «Буфер обмена».
3. Дважды щелкните кнопку «Формат по образцу».
4. Как только вы это сделаете, ваш курсор превратится в кисть.
5. Вы можете применить это форматирование в любом месте вашего листа, на другом листе или даже в другой книге.

72. Переименуйте рабочий лист

Я всегда считал, что это быстрее, чем использование сочетания клавиш для изменения имени листа . Все, что вам нужно сделать, это дважды щелкнуть вкладку листа и ввести новое имя.

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

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

73. Ручка наполнения

Я уверен, что любители ярлыков всегда используют сочетания клавиш для перетаскивания формул и значений в ячейках вниз . Но использование дескриптора заполнения более впечатляет, чем использование горячей клавиши.

1. Выберите ячейку, в которой есть формула или значение, которое вы хотите перетащить.
2. Дважды щелкните небольшой квадратный прямоугольник в правом нижнем углу ограничивающей рамки ячейки.

Этот метод работает только в том случае, если у вас есть значения в соответствующем столбце и работает только в вертикальном направлении.

74. Спрятать ленту

Если вы хотите работать в режиме, не отвлекаясь, вы можете сделать это, свернув ленту Excel.

Просто дважды щелкните активную вкладку на ленте, и лента свернется. Если вы хотите развернуть его, дважды щелкните по нему еще раз.

75. Редактировать фигуру

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

Вы также можете использовать этот метод для редактирования и ввода текста в текстовое поле или заголовок диаграммы .

76. Ширина столбца

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

Тот же метод можно использовать для автоматической регулировки ширины линий.

77. Перейти к последней ячейке

Этот совет может быть полезен, если вы работаете с большим набором данных. Двойным щелчком мыши можно перейти к последней ячейке диапазона, содержащей данные.

Вам нужно нажать на правый край активной ячейки, чтобы пойти вправо, и на левый край, если вы хотите пойти налево.

78. Форматирование диаграмм

Если вы используете Control + 1, чтобы открыть параметры форматирования диаграммы, то я уверен, вам понравится этот совет. Все, что вам нужно сделать, это дважды щелкнуть границу диаграммы, чтобы открыть опцию форматирования.

79. Дважды щелкните сводную таблицу.

Допустим, кто-то прислал вам сводную таблицу без исходных данных . Как вы уже знаете, Excel хранит данные в сводном кеше перед созданием сводной таблицы.

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

80. Перетащите меню

В Excel есть раскрывающееся меню, вызываемое правой кнопкой мыши, о котором знают немногие пользователи. Чтобы использовать это меню, просто выберите ячейку или диапазон ячеек, затем щелкните правой кнопкой мыши и, удерживая ее, перетащите выделение в другое место.

81. Место сохранения файла по умолчанию

Обычно, работая в Excel, я создаю более 15 файлов Excel каждый день. И если я сохраню каждый из этих файлов на свой рабочий стол, это будет выглядеть отвратительно. Чтобы это исправить, я изменил папку по умолчанию, чтобы сохранить книгу, и вы можете сделать это здесь.

1. Сначала перейдите на вкладку «Файл» и откройте параметры Excel.
2. В параметрах Excel перейдите в категорию «Сохранить».
3. Теперь есть панель ввода, где вы можете изменить местоположение локального файла по умолчанию.
4. На этой панели ввода отредактируйте адрес местоположения и в конце нажмите «ОК».

С этого момента, когда вы открываете диалоговое окно «Сохранить как», Excel будет показывать вам указанное вами местоположение.

82. Отключить заставку.

Я уверен, что вы, как и я, ненавидите, когда открываете Microsoft Excel (или любое другое приложение Office) и видите всплывающее окно-заставку. Это займет время в зависимости от скорости вашей системы и установленных вами надстроек. Вот шаги, чтобы отключить начальный экран в Microsoft Office.

1. Сначала перейдите на вкладку «Файл» и откройте параметры Excel.
2. В настройках Excel перейдите в категорию «Общие».
3. Из этой опции перейдите в «Параметры запуска» и снимите флажок «Показывать заставку при запуске этого приложения».
4. В конце нажмите ОК.

С этого момента при каждом запуске Excel книга открывается напрямую, без отображения заставки.

83. Вкладка «Разработчик»

Прежде чем приступить к написанию кодов VBA, первое, что вам нужно сделать, это активировать вкладку «Разработчик» . Когда вы впервые устанавливаете Microsoft Excel, разработчика не будет. Итак, вам нужно включить его в настройках.

1. Сначала перейдите на вкладку «Файл» и нажмите категорию «Настроить ленту».
2. Теперь в списке вкладок проверьте вкладку «Разработчик» и нажмите «ОК».

Теперь, когда вы вернетесь в окно Excel, на ленте у вас появится вкладка разработчика.

84. Включить макросы

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

1. Сначала перейдите на вкладку «Файл» и выберите категорию «Центр управления безопасностью».
2. Оттуда нажмите «Настройки центра управления безопасностью».
3. Теперь в «Настройки центра управления безопасностью» нажмите на настройки макроса.
4. После этого нажмите «Включить все макросы с уведомлениями».
5. В конце нажмите ОК.

85. Опция автозамены

Если вы вводите много данных в Excel, этот вариант может изменить правила игры для вас. С помощью параметра «Автозамена» вы можете указать Excel заменять одну текстовую строку на другую по мере ее ввода.

Позволь мне привести пример:

Меня зовут «Пунит», но иногда люди пишут его как «Пунит», но правильное написание — первое. Итак, что я могу сделать, это использовать автозамену и попросить Excel изменить «Пунит» на «Пунит». Выполните следующие простые шаги:

1. Прежде всего, перейдите на вкладку «Файл», перейдите к параметрам и нажмите категорию «Правописание» .
2. После этого нажмите «Параметры автозамены», и откроется окно автозамены .
3. Здесь, в этом окне, у вас есть две панели ввода, чтобы указать, какой текст заменить, а какой текст заменить.
4. Введите оба значения, затем нажмите «ОК».

86. Пользовательский список

Подумайте так: у вас есть список из 10 продуктов, которые вы продаете. Всякий раз, когда вам нужно вставить эти названия продуктов, вы можете вставить их, используя собственный список. Позвольте мне рассказать вам, как это сделать:

1. Сначала перейдите на вкладку «Файл», выберите «Параметры» и нажмите категорию «Дополнительно».
2. Теперь перейдите в раздел «Общие» и нажмите «Редактировать пользовательский список…».
3. Теперь в этом окне вы можете ввести список, а также можете импортировать его из диапазона ячеек.
   В конце нажмите ОК.

Теперь, чтобы войти в только что созданный пользовательский список, введите первую запись в списке в ячейку, а затем перейдите к этой ячейке с помощью дескриптора заполнения.

87. Применить таблицу

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

Чтобы применить таблицу к данным, просто используйте сочетание клавиш Ctrl + T и нажмите «ОК».

88. Цвет сетки

Если вас не устраивает цвет сетки ячеек по умолчанию, вы можете просто изменить его несколькими щелчками мыши и выполнить для этого следующие простые шаги:

• Сначала перейдите на вкладку «Файл» и выберите категорию «Дополнительно».
• Теперь перейдите в раздел «Параметры отображения этой книги» и выберите цвет, который вы хотите применить.

• В конце нажмите ОК.

Связанное — Печать линий сетки

89. Закрепить на панели задач

Это одна из моих любимых уникальных установок для экономии времени в долгосрочной перспективе. Дело в том, что вместо перехода в меню «Пуск», чтобы открыть Microsoft Excel, лучше всего указать его на панель задач.

Таким образом, вы можете открыть его, нажав на значок на панели задач.

90. Макрос для QAT

Если у вас есть макрокод, который вам нужно часто использовать. Что ж, самый простой способ запустить код макроса — добавить его на панель быстрого доступа.

1. Сначала перейдите на вкладку «Файл» и выберите категорию «Панель быстрого доступа».
2. После этого в «Выбрать команду из» выберите «Макросы».
3. Теперь выберите макрос (который вы хотите добавить в QAT) и нажмите «Добавить».
4. Оттуда нажмите «Изменить» и выберите значок для кнопки макроса.
5. В конце нажмите ОК.

Теперь у вас есть кнопка на QAT, которую вы можете использовать для запуска только что указанного вами макроса .

91. Выберите ячейки формулы.

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

1. Сначала перейдите на вкладку «Главная» ➜ «Редактировать» ➜ «Найти и выбрать» ➜ «Перейти в раздел «Специальные».
2. В диалоговом окне «Перейти к специальному» выберите формулы и нажмите «ОК».

92. Умножение с помощью специальной вставки

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

93. Выделите повторяющиеся значения.

Что ж, вы можете использоватькод VBA для выделения значений , но самый простой способ — использовать условное форматирование. Вот шаги, которые вам необходимо выполнить:

1. Сначала выберите диапазон, в котором вы хотите выделить повторяющиеся значения.
2. После этого перейдите на вкладку «Главная» ➜ «Стили» ➜ «Правило выделения ячеек» ➜ «Повторяющиеся значения».
3. Теперь в диалоговом окне выберите цвет, который хотите использовать, и нажмите «ОК».

Как только вы нажмете «ОК», все повторяющиеся значения будут выделены.

94. Инструмент быстрого сканирования

Вы когда-нибудь замечали, что когда вы выделяете диапазон ячеек в Excel, внизу выделения появляется небольшой значок? Этот значок называется « Инструмент быстрого сканирования » .

Нажав на этот значок, вы увидите некоторые параметры, представленные на ленте, которые вы можете использовать прямо отсюда, чтобы сэкономить свое время.

95. Команда ПУСК

Да, вы также можете открыть приложение Excel с помощью команды «Выполнить».

• Для этого все, что вам нужно сделать, это открыть «Выполнить» (Window Key + R), а затем ввести в нем «excel».
• В конце нажмите Enter.

96. Открыть конкретный файл

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

1. Откройте «Файл» ➜ «Параметры» ➜ «Дополнительно» ➜ «Общие».
2. Обычно введите местоположение (да, вам нужно ввести) папки, в которой находятся эти файлы, в поле «При запуске открывать все файлы в».

97. Открыть Excel автоматически

Каждый раз, включая ноутбук, я первым делом открываю Excel, и я уверен, что вы делаете то же самое. Что ж, у меня есть идея получше: вы можете добавить Excel в папку автозагрузки вашей системы.

1. Сначала откройте «Проводник», используя клавиши Windows + E.
2. Теперь введите адрес ниже в адресную строку, чтобы открыть папку (измените имя пользователя на свое настоящее имя пользователя).
   • C:ПользователиПользовательПриложениеДанныеРоумингMicrosoftWindowsМеню «Пуск»ПрограммыЗапуск
3. После этого откройте начальный экран, щелкните правой кнопкой мыши приложение Excel и выберите «Открыть местоположение файла».
4. Из местоположения (папка приложения Excel) скопируйте значок приложения Excel и вставьте его в папку «Автозагрузка».

Теперь каждый раз, когда вы открываете свою систему, Excel запускается автоматически.

98. Умный поиск

В Excel есть опция «Умный поиск», с помощью которой вы можете искать текст в Интернете. Все, что вам нужно сделать, это выбрать ячейку или текст внутри ячейки и перейти в «Просмотр» ➜ «Аналитика» ➜ «Умный поиск».

Как только вы нажмете на него, откроется боковая панель, где вы увидите информацию об этом конкретном тексте, который вы выбрали. Идея этой опции состоит в том, чтобы получить информацию, просматривая определения и изображения темы (текста) из разных онлайн-источников.

99. Скриншот

Иногда вам нужно добавить скриншоты в таблицу. И для этого в Excel есть опция, позволяющая мгновенно сделать снимок экрана, а затем вставить его в рабочий лист. Для этого перейдите в ➜ Вставка ➜ Иллюстрации ➜ Вырезка экрана.

Связанное — Камера Excel

100. Найдите сочетание клавиш.

Если вы используете версии Excel 2007–2016, вы можете найти сочетание клавиш, нажав клавишу ALT . Как только вы нажмете ее, на ленте отобразятся клавиши выбора, как показано ниже.

Допустим, вы хотите нажать кнопку « Перенос текста », и клавиша будет ALT H W. Таким же образом вы можете получить доступ ко всем опциям с помощью сочетаний клавиш .

Связанное — Вставить строку