✅ 24 хитростей в Excel, которые вы хотели знать вчера

Содержание

24 хитрости Excel для повседневной работы

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

24 хитрости Excel для повседневной работы

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

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

Уловка №1 в Excel - копирование и вставка формул без изменения ссылок

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

Вот быстрое решение:

Выделите ячейки, которые хотите скопировать.
Зайдите в Дом -> Номер. В раскрывающемся списке Формат числа выберите Текст. Это изменит формат ячейки на текстовый.
Нажмите F2, чтобы перейти в режим редактирования. Теперь, удерживая клавишу Control, нажмите Enter.
Скопируйте ячейки.
Перейдите в ячейку назначения и вставьте.
Измените формат с Текстового на Общий.
Нажмите F2 и нажмите Control + Enter.
И готово !!.

Вот еще один отличный метод, разработанный Excel MVP Томом Уртисом (с использованием функции «Найти и заменить») [благодарность Дэйву Брунсу из ExcelJet за то, что он показал мне этот метод]

Уловка №2 в Excel - не отображается ручка заполнения

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

Вот исправление:

Перейдите в Файл -> Параметры.
В диалоговом окне "Параметры" выберите "Дополнительно".
Установите флажок «Включить дескриптор заполнения и перетаскивание ячейки».

Уловка №3 в Excel - Число как текст

Я зацикливаюсь на этом много раз. Числа отформатированы как текст и не работают в формулах.

Вот быстрое исправление:

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

Многие люди используют апостроф (‘) перед числом, чтобы преобразовать числа в текст. Этот трюк с Excel сработает и в этом случае.

Уловка №4 в Excel - необходимо преобразовывать формулы в значения

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

Скопируйте ячейки.
Щелкните правой кнопкой мыши и выберите «Специальная вставка».
В диалоговом окне «Специальная вставка» выберите «Значения» (Сочетание клавиш - Alt + E + S + V).
Щелкните ОК.

Смотрите также: Узнать больше о вставке специальных ярлыков.

Уловка Excel № 5 - Курсор заедает - Невозможно изменить активную ячейку с помощью клавиш со стрелками

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

В данном случае проблема заключается в том, что вы случайно активировали Scroll Lock.

Выключите его и вперед.

Уловка №6 в Excel - ВПР / СООТВЕТСТВИЕ не работают, даже если есть совпадение

Вы можете видеть, что 2 значения идеально совпадают, но формула VLOOKUP или MATCH все равно говорит, что совпадения нет.

Аргххххх…

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

В обход - используйте ОТДЕЛКА функция. Он удаляет все начальные и конечные пробелы, а также лишние пробелы между словами.

Смотрите также: Многие проблемы можно решить, если вы знаете, как очистить данные в Excel.

Уловка №7 в Excel - хотите скрыть текст в ячейках

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

Сделать это можно двумя способами:

Хороший: Сделайте цвет шрифта текста белым (или другим цветом фона).
Лучшая: Выберите ячейку и нажмите Control + 1. Откроется диалоговое окно «Формат ячеек». На вкладке чисел выберите Пользовательский и введите формат ;;;

Уловка №8 в Excel - нужно копировать только видимые ячейки

Вопрос: Что произойдет, если вы скопируете диапазон ячеек, в котором есть скрытые строки / столбцы, и вставите его куда-нибудь?
Отвечать: Все значения ячеек (видимые + скрытые) копируются и вставляются.
Беспокойство: Я не хочу копировать значения скрытых ячеек.
Решение: Выделите ячейки и нажмите Alt +; (выбираются только видимые ячейки). Теперь вставьте куда угодно, и будут вставлены только видимые ячейки.

Уловка №9 в Excel - выберите все пустые ячейки за один раз

Я часто импортирую данные из разных баз. Многие ячейки в этих данных пусты, и мне нужно вставить 0 или N / A. Быстрый способ выделить все пустые ячейки:

Выделите все ячейки в диапазоне.
Перейдите на главную -> Редактирование -> Найти и выбрать -> Перейти к специальному.
Выберите «Пробелы» в диалоговом окне «Перейти к специальному».
Щелкните ОК.

Это выберет все пустые ячейки. Чтобы ввести 0 во все ячейки, введите 0 (это приведет к вводу значения в активной ячейке) и нажмите Control + Enter.

Уловка №10 в Excel - нужно удалить разрывы строк в тексте

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

Было бы обидно, если бы вам пришлось делать это вручную.

Попробуйте это быстрое исправление:

Выберите данные
Перейдите на главную -> Найти и выбрать -> Заменить (сочетание клавиш - Ctrl + H)
В диалоговом окне «Найти и заменить»:
- Найти что: нажмите Control + J (вы можете не видеть ничего, кроме мигающей точки)
- Заменить на: символ пробела (нажмите пробел один раз)
Нажмите "Заменить все" (убедитесь, что перенос слов включен)

https://www.youtube.com/watch?v=zj_OTwwt7Tg

Смотрите также: Узнайте больше об удивительных вещах, которые функция Find and Replace может сделать для вас.

Уловка №11 в Excel - сортировка не работает при объединении ячеек

Конечно, нет.

Так не было сделано. Но это не значит, что у вас не может быть того, чего вы хотите. Вот замена для Merge and Center, и она называется Center Across Selection.

Вот как правильно объединить ячейки в Excel:

Выберите ячейки, которые вы хотите объединить (только столбцы. Для строк используйте объединение и центрирование).
Нажмите Control + 1, чтобы открыть диалоговое окно Формат ячеек.
Перейдите на вкладку «Выравнивание».
В раскрывающемся списке "По горизонтали" выберите "Центр по выделению".
Щелкните ОК.

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

Уловка №12 в Excel - у данных есть дубликаты

Есть 2 вещи, которые вы можете сделать с повторяющимися данными: Выделите это или Удалите это

Выделите повторяющиеся данные: Выберите данные и перейдите на главную страницу -> Условное форматирование -> Выделить правила ячеек -> Повторяющиеся значения.
- Укажите форматирование, и все повторяющиеся значения будут выделены.
Удалить дубликаты в данных: Выберите данные и перейдите в раздел «Данные» -> «Удалить дубликаты».
- Выберите, если у ваших данных есть заголовки, затем выберите столбец и нажмите OK. Это удаляет дубликаты из списка. Если вы хотите, чтобы исходный список не был поврежден, скопируйте и вставьте данные в другое место, а затем сделайте это.

Связанный: Полное руководство по поиску и удалению дубликатов в Excel

Уловка №13 в Excel - необходимо проверить орфографические ошибки

Нажмите F7, чтобы запустить проверку орфографии.

Вот подробное руководство по использованию проверки орфографии в Excel.

Уловка №14 в Excel - прокрутка больших наборов данных вниз скрывает заголовки столбцов

Есть 2 способа решить эту проблему.

Используйте функцию таблицы Excel: Использование функции таблицы Excel гарантирует, что при прокрутке вниз заголовки всегда будут видны. Рекомендуется всегда использовать таблицу Excel с табличными данными, поскольку она имеет очень мощную и полезную функцию. Узнать больше о таблицах Excel
Использование параметров свободных панелей: Если вы хотите, чтобы было видно больше, чем заголовок (например, в случае подзаголовка или таких значений, как итог, среднее в начале), используйте функцию Excel Freeze Pane. Сделать это:
- Выберите всю строку, над которой вы хотите закрепить строки (или крайнюю левую ячейку в этой строке)
- Перейдите в View -> Freeze Panes -> Freeze Panes
  Это позволит вам всегда видеть фиксированные строки при прокрутке вниз.

Уловка №15 в Excel - трата времени на прокрутку назад и вперед в больших наборах данных

Если вы работаете с огромными наборами данных и вам нужно прокручивать вверх и вниз (или вправо и влево), чтобы ссылаться на некоторые точки данных, есть способ сэкономить много времени и прокрутки - с помощью Разделить окно особенность.

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

Перейдите в Просмотр -> Окно -> Разделить.

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

Уловка №16 в Excel - Формула отладки

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

Отладка формулы может быть болезненной, особенно если она была создана кем-то другим. Вот способ сделать это легко и просто. Чтобы отладить формулу:

Выделите ячейку с формулой.
Перейдите в Формулы -> Аудит формул -> Оценить формулу. (Сочетание клавиш Alt + TUF на клавиатуре).
Нажмите «Оценить», чтобы просмотреть шаги, по которым формула вычисляется в Excel.

Хотите отладить часть формулы - Вот еще один способ использования клавиши F9

Уловка Excel №17 - Excel вставляет ссылку на ячейку при редактировании формулы

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

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

Уловка Excel № 18 - Слишком много именованных диапазонов (не запоминаю имена)

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

НЕТ!!

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

Вопрос к тебе? Что произойдет, если у вас много именованных диапазонов, и вы уменьшите масштаб ниже 40%? Я оставлю это на ваше усмотрение.

Уловка №19 в Excel - ошибка везде

Ошибка - это одно уродливое пятно в вашей таблице. Но иногда ошибки - необходимое зло. Вы можете захотеть, чтобы ваша формула возвращала ошибку (скажем, когда искомое значение не найдено в ВПР). Но вам не обязательно смотреть на одного из них. Просто используйте ЕСЛИОШИБКА.

ЕСЛИОШИБКА не работает для Excel версии 2003 и более ранних. В этом случае используйте комбинацию IF и ISERROR.

Уловка №20 в Excel - нужно быстро удалить все комментарии

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

Выделите все ячейки.
Перейдите на главную -> Редактирование -> Найти и выбрать -> Перейти к специальному.
Выберите Комментарии в диалоговом окне «Перейти к специальному».
Щелкните ОК.

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

Уловка №21 в Excel - переключение только между таблицами Excel

Вы один из тех, кто одновременно открывает миллионы приложений.

У меня часто одновременно открываются Google Chrome, Excel, PowerPoint, Kindle, Mozilla Firefox, Email и многие другие приложения.

Большинство из нас используют Alt + Tab для циклического просмотра различных открытых приложений, но что, если вы хотите циклически просматривать только открытые книги Excel. Использовать Control + Tab вместо.

Уловка №22 в Excel - отображение числа с текстом

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

Перейдите к ячейке с числами (или выберите все ячейки, в которых вы хотите применить это).
Нажмите Control + 1.
Во вкладке Number перейдите в Custom (на левой панели).
Измените General на General «Миллион».

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

Уловка №23 в Excel - быстрая прокрутка вправо в огромном наборе данных

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

Обычный способ 1: используйте клавишу со стрелкой вправо (и закройте каждый столбец один за другим).
Обычный способ 2: оставьте клавиатуру, возьмите мышь, выберите полосу прокрутки и выполните прокрутку.
Вот третий необычный способ - использовать Alt + PageDown Key. Он делает то же, что и Page down для строк. Он перескакивает на 20 столбцов за раз.

Уловка № 24 в Excel - страдание от медленной работы с электронными таблицами

Все мы прошли через это. Таблицы Excel имеют тенденцию становиться чертовски медленными.

Некоторые шаги, приведенные ниже, могут помочь ускорить процесс. [Я написал более подробную статью о том, как ускорить работу с электронными таблицами. Щелкните здесь, чтобы прочитать]

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

Это 24 моих лучших трюка в Excel (в произвольном порядке). Я уверен, что их еще сотни, но это не так много, что человек может написать за один раз !! 😉

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

Полезные ресурсы Excel: