22 советов по Excel, как стать профессионалом в области электронных таблиц

На планете Земля очень и очень мало людей, которые могли бы сказать, что они полностью освоили каждую сложную мелочь в Microsoft Excel. Это первое в мире приложение для работы с электронными таблицами, которое уже 35 лет является отраслевым стандартом и заменило некогда почтенное Lotus 1-2-3, первое приложение-убийца для ПК в 1980-х годах.

Доминирование Microsoft Excel в качестве электронной таблицы еще предстоит проверить, конечно же, не Quattro Pro от Corel (все еще продается сегодня в WordPerfect Office Pro), инструментами с открытым исходным кодом от Apache или LibreOffice или даже Google Sheets (часть электронных таблиц Google Drive).).

Есть причина для этого. Excel - это больше, чем бренд, который знают все: он мощный. Он делает практически все, что можно попросить в электронной таблице. Текущая версия Excel 2019, доступная в Microsoft Office 2019 как часть подписки на Microsoft 365 и других методов, является выбором редакции PCMag.

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

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

Разрывы строк и перенос текста

Ввод в ячейки электронной таблицы может быть неприятным, так как по умолчанию для набираемого вами текста текст будет продолжаться вечно, без перехода на новую строку. Вы можете изменить это. Создайте новую строку, набрав Alt + Enter (только нажатие Enter приведет к выходу из ячейки). Или выберите параметр « Обтекание текстом» на вкладке «Главная» в верхней части экрана, что означает, что весь текст переносится прямо на край ячейки, в которой вы находитесь. Измените размер строки / столбца, и текст будет перенесен в соответствии с размером.



Ctrl + Shift для выбора

Есть гораздо более быстрые способы выбора набора данных, чем с помощью мыши и перетаскивания курсора. Нажмите в первой ячейке, которую вы хотите выбрать, и удерживайте Ctrl + Shift, затем нажмите стрелку вниз, чтобы получить все данные в столбце ниже, стрелку вверх, чтобы получить все данные выше, или стрелку влево или вправо, чтобы получить все в строка (влево или вправо, конечно). Объедините направления, и вы можете получить целый столбец, а также все в строках слева или справа. Он будет выбирать только ячейки с данными (даже невидимые данные). Если вы используете Ctrl + Shift + End, курсор переместится в самую нижнюю правую ячейку с данными, выбирая все между ними. Так что, если курсор находится в верхней левой ячейке (A1), это все. Еще быстрее: Ctrl + Shift + * (звездочка) выделит весь набор данных независимо от того, какая ячейка выбрана. ( Изображение от xuuxuu от Pixabay )



Автозаполнение

Это не просто, но так легко не заметить. Вы начинаете вводить ряд повторяющихся вещей, таких как даты (1/1/20, 1/2/20, 1/3/20 и т. Д.), И вы знаете, что у вас долгий день. Вместо этого начинайте серию и перемещайте курсор на экране в нижнюю правую часть последней ячейки - маркер заполнения. Когда он превратится в знак плюс (+), нажмите и перетащите вниз, чтобы выбрать все ячейки, которые нужно заполнить. Они будут волшебным образом заполнены, используя шаблон, который вы начали. И это также может идти вверх по столбцу, или влево или вправо по ряду.

Еще лучше - вы можете выполнять автоматическое заполнение без какого-либо паттерна. Снова выберите ячейку или ячейки, перейдите к ручке заливки, щелкните и перетащите. Вы получите меню опций. Чем больше данных вы вводите вначале, тем лучше будет вариант «Заполнить серию» при создании параметров автозаполнения. Проверьте это руководство Microsoft.



Flash Fill

Flash Fill будет грамотно заполнять столбец на основе шаблона данных, который он видит в первом столбце (это помогает, если верхняя строка является уникальной строкой заголовка). Например, если в первом столбце указаны все номера телефонов, отформатированные как «2125034111», и вы хотите, чтобы все они выглядели как «(212) -503-4111», начните вводить текст. По второй ячейке Excel должен распознать шаблон и отобразить то, что он считает нужным. Просто нажмите Enter, чтобы использовать их. Это работает с числами, именами, датами и т. Д. Если вторая ячейка не дает точного диапазона, введите еще немного - шаблон может быть трудно распознать. Затем перейдите на вкладку Data и нажмите кнопку Flash Fill. Проверьте этот учебник Microsoft для получения дополнительной информации.



Текст в столбцы

Скажем, у вас есть столбец, полный имен, сначала рядом с последним, но вы хотите два столбца, которые разбивают их. Выберите данные, затем на вкладке « Данные » (вверху) нажмите « Текст в столбцы». Выберите разделить их разделителями (на основе пробелов или запятых - отлично подходит для значений данных CSV) или фиксированной ширины. Фиксированная ширина используется, когда все данные помещены в первый столбец, но разделены фиксированным числом пробелов или периода. Остальное похоже на волшебство, с дополнительными опциями для определенных номеров.



Специальная паста для транспонирования

У вас есть куча строк. Вы хотите, чтобы они были столбцами. Или наоборот. Вы бы сходили с ума, двигая вещи по клеткам. Скопируйте эти данные, выберите « Специальная вставка», установите флажок « Транспонировать» и нажмите «ОК», чтобы вставить в другую ориентацию. Столбцы становятся рядами, строки становятся столбцами.



Несколько ячеек, одни и те же данные

По какой-то причине вам, возможно, придется снова и снова писать одно и то же в ячейках рабочего листа. Это мучительно. Просто щелкните весь набор ячеек, либо перетаскивая курсор, либо удерживая клавишу Ctrl при нажатии каждой из них. Введите его в последнюю ячейку, затем нажмите Ctrl + Enter - и то, что вы ввели, попадет в каждую выбранную ячейку.



Специальная паста с формулами

Допустим, у вас есть огромное количество чисел в десятичном формате, которые вы хотите показать в процентах. Проблема в том, что цифра 1 не должна быть 100%, но это то, что Excel дает вам, если вы просто нажмете кнопку Percent Style (или нажмете Ctrl-Shift-%). Вы хотите, чтобы 1 был 1%. Таким образом, вы должны разделить его на 100. Вот тут и появляется Paste Special.

Сначала введите 100 в ячейку и скопируйте ее. Затем выберите все числа, которые вы хотите переформатировать, выберите Специальная вставка, нажмите кнопку-переключатель « Разделить », и бум динамит: у вас есть числа, преобразованные в проценты. Это также работает для мгновенного сложения, вычитания или умножения чисел, очевидно.



Использовать графику в диаграммах

Вы можете поместить графику в любой элемент диаграммы Excel. Любой элемент. Каждый столбик, кусок пирога и т. Д. Может поддерживать свое собственное изображение. Например, выше, на круговой диаграмме есть флаг штата Южная Дакота (размещается путем выбора среза, с помощью всплывающего меню «Параметры серии» и выбора «Заливка рисунка или текстуры»), а также встроенного логотипа PCMag (размещается с Вставьте вкладку «Изображения». Вы можете даже пойти с «без заливки», что привело к отсутствию среза.

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



Сохранить диаграммы как шаблоны

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

Как только график будет совершенен, щелкните по нему правой кнопкой мыши. Выберите Сохранить как шаблон. Сохраните файл с расширением CRTX в папке шаблонов Microsoft Excel по умолчанию. Как только вы это сделаете, применение шаблона - это торт. Выберите данные, которые вы хотите отобразить, перейдите на вкладку «Вставка», нажмите « Рекомендованные диаграммы», затем вкладку « Все диаграммы » и папку «Шаблоны». В поле «Мои шаблоны» выберите тот, который нужно применить, и нажмите « ОК».

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



Работа с клетками на листах

Этот, называемый 3D Sum, работает, когда у вас есть несколько листов в книге, которые имеют одинаковую базовую схему, например, квартальные или годовые отчеты. Например, в ячейке B3 у вас всегда есть сумма в долларах за одну и ту же соответствующую неделю с течением времени.

На новом листе в книге перейдите к ячейке и введите формулу, например = sum ('Y1: Y10'! B3). Это указывает на формулу СУММА (суммируя) для всех листов с названиями от Y1 до Y10 (то есть на 10 лет) и с учетом ячейки B3 в каждом. Результатом будет сумма всех 10 лет. Это хороший способ создать основную электронную таблицу, которая ссылается на постоянно меняющиеся данные.



Скрыть в простом виде

Строку или столбец легко спрятать - просто выберите все это, щелкнув заголовок буквы или цифры, щелкните правой кнопкой мыши и выберите «Скрыть». (Вы можете отобразить, выбрав столбцы по обе стороны одновременно, щелкнув правой кнопкой мыши и выбрав «Показать»). Но что, если у вас есть небольшой фрагмент неудобно размещенных данных, которые вы хотите скрыть, но при этом вы все еще хотите работать? Легко. Выделите ячейки, щелкните правой кнопкой мыши и выберите « Формат ячеек». На вкладке Number вверху перейдите в категорию и выберите « Custom ». Введите три точки с запятой (;;;) в поле Тип:. Нажмите ОК. Теперь числа не видны, но вы все равно можете использовать их в формулах.



Скрыть весь лист

Ваша типичная рабочая книга Excel - файл, в котором вы работаете - может быть загружена множеством рабочих листов (каждый лист обозначен вкладкой внизу, которую вы можете назвать). Если хотите, скрывайте лист, а не удаляйте его, делая его данные доступными не только для справки, но и для формул на других листах в книге. Щелкните правой кнопкой мыши вкладку нижнего листа и выберите « Скрыть». Когда вам нужно найти его снова, вы должны перейти на вкладку View сверху, нажать Unhide и выбрать имя листа из всплывающего списка.

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



Использовать личную книгу для макросов

Когда вы откроете всю книгу целиком, вы, вероятно, увидите список книг, которые вы не знали, что скрыли: файл Personal.XLSB. На самом деле это личная рабочая книга Excel, созданная для вас; он открывается как скрытая рабочая книга при каждом запуске Excel. Причина, чтобы использовать это? Макросы. Когда вы создаете макрос, он не работает для каждой электронной таблицы, которую вы создаете по умолчанию (как это происходит в Word) - макрос привязывается к книге, в которой он был создан. Однако, если вы храните макрос в Personal.XLSB, он будет доступен постоянно, во всех ваших электронных таблицах. Хитрость заключается в том, что при записи макроса в поле «Сохранить макрос в» выберите «Персональная книга макроса». (Вы можете записать макрос, включив вкладку «Разработчики» - перейдите на вкладку «Файл», выберите «Параметры», нажмите «Настроить ленту», затем в поле «Основные вкладки» выберите «Разработчики» и нажмите «ОК».)



Pivot! Pivot!

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

Чтобы создать его, убедитесь, что все столбцы и строки озаглавлены так, как они должны быть, а затем выберите « Сводная таблица» на вкладке «Вставка». Еще лучше, попробуйте параметр Рекомендуемые сводные таблицы, чтобы увидеть, может ли Excel выбрать правильный вид для вас. Или попробуйте PivotChart, которая создает сводную таблицу со включенным графиком, чтобы ее было легче понять.



Условно формат

Глядя на огромный объем данных и интересно, где основные моменты? Кто имеет наивысшую (или низшую) оценку, кто входит в пятерку и т. Д.? Условное форматирование Excel будет делать все, от наложения границ на выделенные участки до цветового кодирования всей таблицы. Он даже построит график в каждой ячейке, чтобы вы могли визуализировать верхнюю и нижнюю часть диапазона чисел. (Выше, самые высокие цифры обозначены быстрым зеленым, самые низкие - красным, с остановкой между ними.) Используйте подменю Highlighted Cells Rules, чтобы создать больше правил для поиска вещей, таких как текст, содержащий определенную строку слова, повторяющиеся даты, повторяющиеся значения и т. д. Опция даже больше / меньше чем, поэтому вы можете сравнивать изменения чисел.



Проверьте данные, чтобы сделать выпадающие

Создание электронной таблицы для использования другими? Если вы хотите создать выпадающее меню выбора для использования в определенных ячейках (чтобы они не могли его испортить!), Это просто. Выделите ячейку, перейдите на вкладку «Данные» и нажмите « Проверка данных». Под «Разрешить:» выберите « Список ». Затем в поле «Source:» введите список с запятыми между опциями. Или нажмите кнопку рядом с полем «Источник» и вернитесь на тот же лист, чтобы выбрать ряд данных - это лучший способ обработки больших списков. Вы можете скрыть эти данные позже, они все равно будут работать. Проверка данных - это также хороший способ ограничить ввод данных - например, указать диапазон дат, и люди не смогут вводить даты до или после того, что вы укажете. Вы даже можете создать сообщение об ошибке, которое они увидят.



Вставка скриншота

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



Вставить данные Excel в Word

Тридцать пять лет назад идея размещения данных из Excel в Word или PowerPoint была ошеломляющей в мире Office Suite. Сегодня в этом нет ничего. Если вы берете ячейки данных или полноценную графическую диаграмму, скопируйте и вставьте в другую программу. Следует помнить, что это процесс связывания и вставки - если вы измените данные в электронной таблице, они также изменятся в Word DOC или PowerPoint PPT. Если вы не хотите этого, вставьте его в качестве рисунка. Для этого используйте собственный инструмент вставки Word. Или, принимая его из Excel, перейдите на вкладку «Главная» вверху, выберите меню «Копировать» и используйте параметр « Копировать как изображение». Затем вы можете вставить графику в любую программу.



Используйте $ для предотвращения сдвига

Когда вы пишете формулу, вы ссылаетесь на ячейки по их положению, например, A1. Если вы скопируете формулу и вставите ее в следующую ячейку вниз, Excel сместит эту ячейку, на которую ссылаются, вместо этого будет указано A2. Чтобы предотвратить смещение, используйте знак доллара ($). Например, введите $ A1, обрежьте и вставьте его в новую ячейку, что предотвращает смещение в столбце (A); $ 1 предотвращает смещение в строке (1), а $ A $ 1 предотвращает изменение смещения в любом направлении при копировании формулы.

Это удобно, когда у вас есть одна ячейка для использования в целом ряде формул. Скажем, вы хотите разделить все на 100. Вы можете сделать формулу, например = (A1 / 100), но это означает, что вы не можете легко изменить 100 по всем направлениям. Поместите 100 в ячейку B1 и используйте = (A1 / B1) - но затем, когда вы вырезаете и вставляете его, оно превращается в = (A2 / B2), затем = (A3 / B3) и т. Д. $ Исправляет следующее: = (A1 / $ B $ 1) можно вырезать и вставить в строку, но ссылка $ B $ 1 никогда не меняется. Затем вы можете изменить значение 100 в ячейке по мере необходимости, чтобы поэкспериментировать с другими изменениями.



Выполнить быстрый анализ

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



Отличная комбинация клавиш Excel

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

CTRL +; - Вставляет сегодняшнюю дату.

Ctrl + Shift +: вставляет текущее время (двоеточие - это то, что показывается в часах, например, 12:00).

Ctrl + Shift + # - изменяет формат даты.

Ctrl + 5 - применяет зачеркивание к тексту в ячейке.

Ctrl + 0 - скрывает текущий столбец.

Ctrl + 9 - скрывает текущую строку.

Ctrl + F6 - переключение между открытыми книгами (то есть открытие файлов Excel в разных окнах).

Ctrl + ` - это знак акцента, вверх по клавише 1. Эта комбинация переключает представление на листе, чтобы показать все формулы.

Ctrl + PageUp или PageDown - быстрое переключение между листами в текущей открытой книге.

F2 - начать редактирование текущей выбранной ячейки (намного быстрее, чем двойной щелчок).

Shift + F10 - открывает контекстное меню для ячейки, в которой вы находитесь.



16 советов по Microsoft Word, которые нужно изучить сейчас

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