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

Допустим, вы меняете не только обтекание в ячейке, но и весь внешний вид — шрифт, цвет, что угодно. И вы хотите применить его ко многим, многим другим ячейкам. Хитрость заключается в инструменте Format Painter , который находится на вкладке «Главная» и выглядит как кисть.

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

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

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

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

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

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

Это работает с числами, именами, датами и т. д. Если во второй ячейке нет точного диапазона, введите еще немного — шаблон может быть трудно распознать. Затем перейдите на вкладку «Данные» и нажмите кнопку «Быстрая заливка». Ознакомьтесь с этим руководством Microsoft, чтобы узнать больше.

Есть гораздо более быстрые способы выбора набора данных, чем использование мыши и перетаскивание курсора, особенно в электронной таблице, которая может содержать сотни тысяч строк или столбцов. Щелкните первую ячейку, которую вы хотите выбрать, и удерживайте нажатой клавишу Ctrl+Shift , затем нажмите стрелку вниз, чтобы получить все данные в столбце ниже, стрелку вверх , чтобы получить все данные выше, или стрелку влево или вправо , чтобы получить все данные. ряд (налево или направо, конечно). Объедините направления, и вы можете получить целую колонку, а также все в строках слева или справа. Он будет выбирать только ячейки с данными (даже невидимыми данными).

Если вы используете Ctrl+Shift+End , курсор переместится в самую нижнюю правую ячейку с данными, выбрав все между ними, даже пустые ячейки. Итак, если курсор находится в верхней левой ячейке (A1), это все.

Ctrl+Shift+* (звездочка) может быть быстрее, так как будет выбран весь непрерывный набор данных ячейки, но остановятся на пустых ячейках.

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

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

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

Это также работает с формулами и изменит ссылки на ячейки для работы с любой строкой/столбцом, в которой находятся другие ячейки.

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

Вы хотите, чтобы 1 был 1%. Так что вы должны разделить его на 100. Вот тут-то и пригодится Paste Special.

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

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

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

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

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

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

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

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

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

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

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

Когда вы отобразите всю книгу целиком, вы, вероятно, увидите в списке книгу, которую вы не знали, что скрыли: файл Personal.XLSB. На самом деле это личная рабочая книга Excel, созданная для вас; она открывается как скрытая книга при каждом запуске Excel. Причина его использования? Макросы .

Когда вы создаете макрос, он не работает в каждой отдельной электронной таблице, которую вы создаете по умолчанию (как это происходит в Microsoft Word) — макрос привязывается к книге, в которой он был создан. Однако, если вы сохраняете макрос в Личном. XLSB, он будет доступен все время во всех ваших файлах электронных таблиц.

Хитрость заключается в том, что при записи макроса в поле «Сохранить макрос в» выберите «Личная книга макросов». (Запишите макрос, включив вкладку «Разработчики»: перейдите на вкладку «Файл» , выберите « Параметры » , нажмите « Настроить ленту », затем в поле « Главные вкладки » установите флажок « Разработчики » и нажмите « ОК ».)

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

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

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

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

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

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

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

Ctrl+; — вставляет сегодняшнюю дату.
Ctrl+Shift+: — вставляет текущее время (двоеточие — это то, что стоит в показаниях часов, например, 12:00).
Ctrl+Shift+# — изменяет формат даты.
Ctrl+5 — зачеркивание текста в ячейке.
Ctrl+0 — скрывает текущий столбец.
Ctrl+9 — скрывает текущую строку.
Ctrl+F6 — переключение между открытыми книгами (то есть открытие файлов Excel в разных окнах).
Ctrl+` — это знак ударения вверх на клавишу 1. Эта комбинация переключает представление на листе для отображения всех формул.
Ctrl+PageUp или PageDown — быстрый переход между листами в текущей открытой книге.
F2 — начать редактирование текущей выбранной ячейки (гораздо быстрее, чем двойной щелчок).
Shift+F10 — открывает контекстное меню для ячейки, в которой вы находитесь.

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

Держите палец на Ctrl и щелкните столько ячеек, сколько хотите, строка состояния будет продолжать показывать сумму для всех ячеек. (Щелкните ячейку с буквами/словами в качестве содержимого, она будет проигнорирована.) Еще лучше, щелкните правой кнопкой мыши строку состояния, чтобы открыть меню «Настройка строки состояния», и вы можете добавить другие элементы, которые можно быстро вычислить, например как просмотр среднего или подсчета того, сколько ячеек вы щелкнули (или числового подсчета, то есть, сколько ячеек, которые вы щелкнули, на самом деле имеют числа).

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

Перейдите на вкладку View и найдите Freeze Panes . Вы можете просто закрепить верхнюю строку (выберите « Закрепить верхнюю строку» ) или первый столбец (выберите « Закрепить первый столбец »). Вы можете сделать и то, и другое одновременно, щелкнув ячейку B2 и просто выбрав Freeze Panes . Вот где это весело — выберите любую другую ячейку, а также заморозьте все панели выше и слева от нее. Например, выберите ячейку C3, и две строки выше и два столбца слева не будут прокручиваться. Вы можете видеть это на скриншоте выше, обозначенном затемненными линиями сетки.

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

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

Думаете, вы знаете все о Microsoft Word? Вот несколько скрытых приемов и лайфхаков, которые вам нужно знать о текстовом редакторе Redmond.