На этой странице мы постарались собрать и кратко описать основные приемы, трюки и хитрости, основное предназначение которых — увеличение эффективности работы с электронными таблицами Excel. Все советы, а их более 50, опробованы во всех версиях Excel, начиная с 2003 и заканчивая Excel 2013. Подробное описание тех или иных приемов ищите на страницах нашего сайта.
Изучите «горячие клавиши»! Умелое применение «горячих клавиш» позволит Вам сэкономить значительное время при работе с таблицами Excel. Скачайте любой список «горячих клавиш» из 3-х вариантов, представленных ниже.
Чтобы скрыть от посторонних глаз один из листов книги Excel существует два способа решения этого вопроса.
Вариант 1: На ленте Главная в разделе Ячейки выберите Формат → Видимость: Скрыть или отобразить → Скрыть лист
Вариант 2: Щелкните правой кнопкой мыши на ярлыке нужного листа и выберите Скрыть.
Если вам необходимо выровнять на листе книги Excel несколько объектов или фигур, такие как диаграммы, рисунки или картинки, то нужно выделить все выравниваемые объекты или фигуры, щелкая по каждому левой кнопкой мыши и удерживая клавишу Ctrl. Если вы будете использовать комбинацию клавиш Ctrl+A, то на листе будут выделены все объекты. Чтобы снять выделение с объектов, которые не требуют выравнивания, нажмите кнопку Ctrl и снимайте выделение левой кнопкой мыши. Выделив внеобходимые объекты или фигуры, перейдите на контекстную вкладку Средства рисования и в разделе Упорядочить кликните на пункт Выровнять и выберите необходмый параметр выравнивания.
Для того чтобы отключить отображение ошибок в формулах, перейдите на вкладку Файл → Параметры → Формулы → Правила поиска ошибок и отключите те ошибки, которые, как вы считаете, не должны отображаться.
Чтобы сохранить настройки фильтра, таким образом, чтобы ими можно было воспользоваться позднее, используйте пользовательские представления: перейдите на вкладку Вид, далее выберите группу Режимы просмотра книги, и выберите Представления.
Чтобы найти в ячейках листа книги Excel интересующие вас формулы, нажмите сочетание клавиш Ctrl+G, затем нажмите Выделить и отметьте условия, на основании которых будет выполнен поиск.
Чтобы быстро выполнить очистку форматов в выделенном диапазоне ячеек выполните следующие действия: Главная → Редактирование → Очистить → Очистить форматы.
Чтобы при перемещении диаграммы разместить её в соответствии с границами ячеек, удерживайте нажатой клавишу Alt при перемещении графика.
Чтобы создать динамический (изменяющийся совместно с источником) микро-график из вашего обычного графика, используйте инструмент Камера. Чтобы добавить камеру на панель быстрого доступа выполните Файл → Параметры → Панель быстрого доступа. Выберите Все команды → Камера и добавьте инструмент на панель.
Объедините два различных типа диаграмм, если вам кажется, что для представления данных одного типа не достаточно. Добавьте еще один ряд данных на лист, а затем щелкните правой кнопкой мыши по нему и измените тип диаграммы.
Чтобы изменить порядок элементов в диаграмме, просто щелкните мышью на Y-оси, нажмите Ctrl+1, и установите флажки Обратный порядок категорий и Вертикальная ось пересекает в максимальной категории.
Для изменения символа маркера или «пузырей» в диаграмме на вашу любую форму или картинку, просто нарисуйте в любом месте листа с помощью панели инструментов Рисование форму или вставьте картинку, а затем скопируйте её, нажав Ctrl+С, затем перейдите к диаграмме, выберите маркеры (или пузырьки) и нажмите Ctrl+V.
Чтобы уберечь себя от различных проблем с визуализацией данных, старайтесь избегать таких диаграмм: объемные графики с плоскостями (без накопления), лепестковые диаграммы, 3D линии, 3D гистограммы с несколькими рядами данных, кольцевые графики с более чем двумя рядами данных.
Выполните следующие 6 шагов для улучшения внешнего вида вашей диаграммы.
Чтобы получить имя из полного имени, отчества и фамилии («Иван» из «Иван Иванович Иванов») используйте формулу — =ЛЕВСИМВ("Ф.И.О.";НАЙТИ(" ";"Ф.И.О.")-1).
Чтобы рассчитать выплаты по кредиту: ПЛТ(процентная_ставка;количество_выплат;сумма_кредита).
Чтобы получить N-ное наибольшее число в диапазоне: НАИБОЛЬШИЙ(диапазон;N).
Чтобы получить N-ное наименьшее число в диапазоне: =НАИМЕНЬШИЙ(диапазон;N).
Подсчитать количество слов в ячейке: =ДЛСТР(СЖПРОБЕЛЫ(текст))-ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(текст);" ";""))+1.
Для подсчета количества положительных значений в диапазоне: =СЧЁТЕСЛИ(диапазон;">0").
Для удаления лишних пробелов в тексте: =СЖПРОБЕЛЫ(текст).
Чтобы узнать возраст человека на основе даты рождения, например в формате 39 л., 6 мес., 25 дн., воспользуйтесь недокументированной функцией РАЗНДАТ():
=РАЗНДАТ(дата_рождения;СЕГОДНЯ();"y")&"л.," & РАЗНДАТ(дата_рождения;СЕГОДНЯ();"ym")&" мес., " & РАЗНДАТ(дата_рождения;СЕГОДНЯ();"md")&" дн.".
Чтобы получить обычную дробь от числа (например, 1/8 из 0,125): =ТЕКСТ(десятичная_дробь;"?/?").
Чтобы найти частичные совпадения с помощью функции ВПР: =ВПР(«абв*»; диапазон_просмотра; искомый столбец).
Чтобы сделать все начальные буквы прописными в тексте (например, получить «Иван Иванович Иванов» из «иван иванович иванов» или из «ИВАН ИВАНОВИЧ ИВАНОВ»): =ПРОПНАЧ(текст).
Для отладки формул, выберите часть формулы и нажмите клавишу F9, чтобы увидеть результат этой части. Будьте внимательны! Excel заменяет формулу вычисленным значением, чтобы избежать этого выйдите из режима редактирования формулы в ячейке после просмотра значения.
Если вам нужно сохранить начальные нули при вводе данных (например, если вы используете артикул товара в виде 000324 и при вводе артикул превращается в 324), примените к ячейке формат «Текстовый».
Для отображения в ячейке телефонного номера в международном формате (например, (+7)-123-456-78-90) используйте пользовательский формат вида «(+0)-000-000-00-00».
Если вам нужно построить последовательность дат или дней недели воспользуйтесь диалоговым окном Прогрессия. Введите начало прогрессии (январь, янв, понедельник, пн и т.п.) и выполните: Главная → Заполнить (группа Редактирование) → Прогрессия → Автозаполнение.
Чтобы ввести данные в несколько листов одновременно выделите мышью ярлычки нужных листов, удерживая нажатой кнопку Ctrl.
Чтобы поместить текст в ячейке в несколько строк, после ввода каждой строки нажмите Alt+Enter.
Если часто приходится вводить длинные названия организаций и т.п. (например, «Московский центр подготовки, переподготовки и повышения квалификации специалистов водного транспорта»), с помощью настройки параметров автозамены можно сохранить значительное количество времени. Выполните: Файл → Параметры → Правописание → Параметры автозамены. Введите в поле «Заменять:» — москвацентр, в поле «на:» — полное наименование. После ввода в ячейку «москвацентр» Excel автоматически изменит текст на полное наименование из нашего примера (для себя вы, конечно же, выберите другие значения).
Если вы часто копируете данные из одной книги в другую (или даже в другое приложение) воспользуйтесь «Буфером обмена». Буфер может хранить до 24 объектов, и доступен во всех приложениях Microsoft Office. Вызвать буфер обмена можно на ленте Главная.
Чтобы изменить папку, в которую Excel предлагает сохранять файлы (обычно по умолчанию это папка «Мои документы») выполните Файл → Параметры → Сохранение и укажите нужную папку в поле Расположение файлов по умолчанию.
Чтобы переместиться к последней непустой ячейке столбца нажмите Ctrl+↓, к последней непустой ячейке строки – Ctrl+→, к первой непустой ячейке столбца Ctrl+↑, в строке — Ctrl+←.
Чтобы сузить область поиска значений (только в определенном диапазоне) выделите необходимый диапазон и нажмите Ctrl+F.
Чтобы защитить ячейку от ввода неверных данных (например, буквы вместо цифр, очень большие либо очень маленькие значения и т.д.) выполните: вкладка Данные → Проверка данных (группа Работа с данными) и укажите нужные значения в Условиях проверки.
Чтобы выделить данные в ячейках не соответствующие какому-либо критерию, создайте проверку данных (вкладка Данные → Проверка данных (группа Работа с данными) и выберите Обвести неверные данные.
Чтобы отформатировать часть содержимого ячейки, выделите эту часть в строке формул и примените необходимое вам форматирование.
Если вы часто сталкиваются с задачей построения формул и копирования их вниз на десятки или даже сотни ячеек просто дважды щелкните на маркере заполнения в ячейке с формулой, чтобы заполнить весь столбец формулой на всем протяжении диапазона вниз.
При работе с большими таблицами используйте пользовательские представления. Вкладка Вид → Представления → Добавить. Пользовательские представления позволяют быстро переходить от одной набора настроек отображения (в том числе настройки печати, настройки скрытых строки и столбцов, установки фильтров, масштаб) на другой.
Для вставки на лист Excel документа Word выполните: лента Вставка → Объект (группа Текст) → вкладка Из файла и выберите нужный документ Word. Для того чтобы объект автоматически изменялся при изменении документа источника установите галочку Связь с файлом. Для редактирования объекта непосредственно в Excel дважды щелкните на объекте.
Чтобы при заполнении ячеек с использованием маркера заполнения не возникала прогрессия, а ячейки просто копировались, при протягивании за маркер удерживайте нажатой кнопку Ctrl.
Старайтесь всегда присваивать диапазонам и отдельным ячейкам имена. В этом случае вам не придется путаться в абсолютных и относительных ссылках, так как имена всегда имеют абсолютный адрес. Чтобы присвоить имя выделите диапазон или ячейку, введите необходимое имя в поле Имя, которое находится слева от строки формул, и нажмите Enter (последнее условие является обязательным!).
Если в таблице необходимо отформатировать много областей аналогичным форматом, чтобы выбрать не смежные диапазоны нажмите и удерживайте клавишу Ctrl и выделите нужные ячейки с помощью мыши. При применении форматирования все выделенные ячейки будут иметь одинаковый формат.
Если вы давно не работали с таблицей или работаете с незнакомой таблицей, то обычно вы тратите много времени на выяснение, что находится в каждой ячейке (значение или формула). Что бы легко просмотреть все формулы на листе нажмите Ctrl+~ (знак тильда). Чтобы вернутся назад, на обычное отображение листа, нажмите Ctrl+~ еще раз.
Если вы часто используйте однотипное форматирование для разных ячеек создайте для определенного сочетания цвета шрифта, цвета фона, границ, числовых форматов свой стиль ячеек. Выполните вкладка Главная – Стили ячеек (группа Стили) – Создать стиль ячеек (или выберите готовый).
Если вы работаете преимущественно с одной книгой Excel, вы можете настроить программу так, что при запуске Excel эта книга будет отрываться автоматически. Выполните Файл → Параметры → Дополнительно → Общие. Укажите в поле Каталог автозагрузки нужную папку и поместите книгу туда. Имейте в виду, если в указанной папке хранится несколько файлов, Excel будет пытаться открыть их все. Если вы используете книгу как шаблон, имеет смысл присвоить книге атрибут Только для чтения.
Рис 49
Если вы создаете много книг имеющих одинаковую базовую структуру, создайте на её основе шаблон. При сохранении книги выберите в окне Сохранение документа тип файла Шаблон Excel. Убедитесь, что в верхней части окна указана папка Шаблоны. Сохраните шаблон под нужным вам именем. Теперь при выполнении команды Файл → Создать, вы можете выбрать ваш шаблон.
