В этой статье мне хотелось бы представить вам самые эффективные приемы работы в Microsoft Excel, собранные мной за последние 10 лет работы над проектами и проведения тренингов по этой замечательной программе. Здесь нет описания суперсложных технологий, но есть приемы на каждый день - простые и эффективные, описанные без "воды" - только "сухой остаток". На освоение большинства из этих примеров у вас уйдет не более одной-двух минут, а вот сэкономить они вам помогут гораздо больше.
Быстрый переход к нужному листу
Случается ли вам работать с книгами Excel, состоящими из большого количества листов? Если их больше десятка, то каждый переход к очередному нужному листу сам по себе становится маленькой проблемой. Простое и элегантное решение такой задачи - щелкнуть в левом нижнем углу окна по кнопкам прокрутки ярлычков листов не левой, а правой кнопкой мыши - появится оглавление книги с полным списком всех листов и на нужный лист можно будет перейти в одно движение:
Это намного быстрее, чем прокручивать ярлычки листов этими же кнопками в поисках нужного.
Копирование без повреждения форматирования
Сколько сотен (тысяч?) раз я видел эту картину, стоя за спиной своих слушателей во время тренингов: пользователь вводит формулу в первую ячейку затем и "протягивает" ее на весь столбец, нарушая форматирование нижерасположенных строк, поскольку такой способ копирует не только формулу, но и формат ячейки. Соответственно, дальше приходится вручную исправлять повреждения. Секунда на копирование и потом 30 - на починку испорченного копированием дизайна.
Начиная с Excel 2002, есть решение этой проблемы - простое и изящное. Сразу после копирования (протаскивания) формулы на весь столбец, нужно воспользоваться смарт-тегом - небольшим значком, который временно появляется в правом нижнем углу диапазона. Нажатие на него выведет список возможных вариантов копирования, где и можно выбрать Копировать только значения ( Fill without formatting). В этом случае формулы копируются, а форматирование - нет:
Копирование только видимых ячеек
Если вы работаете в Microsoft Excel больше недели, то должны были уже сталкиваться с подобной проблемой: в некоторых случаях при копировании-вставке ячеек их вставляется больше, чем было, на первый взгляд, скопировано. Это может происходить, если копируемый диапазон включал в себя скрытые строки/столбцы, группировки, промежуточные итоги или фильтрацию. Рассмотрим в качестве примера один из таких случаев:
В этой таблице посчитаны промежуточные итоги и сделана группировка строк по городам - это легко понять по кнопкам "плюс-минус" слева от таблицы и по разрывам в нумерации видимых строк. Если выделить, скопировать и вставить данные из этой таблицы обычным способом, то мы получим 24 лишних строки. Нам же хочется скопировать и вставить только итоги!
Можно решить проблему, кропотливо выделяя каждую строку итогов и удерживая при этом клавишу CTRL - как для выделения несмежных диапазонов. Но если таких строк не три-пять, а несколько сотен или тысяч? Есть другой, более быстрый и удобный путь:
Выделите копируемый диапазон (в нашем примере - это A1:C29)
Нажмите на клавиатуре клавишу F5 и затем кнопку Выделить ( Special) в открывшемся окне.
Появится окно, позволяющее пользователю выделять не все подряд, а только нужные ячейки:
В этом окне выберите опцию Только видимые ячейки ( Visible cells only) и нажмите ОК.
Полученное выделение теперь можно смело копировать и вставлять. В результате мы получим копию именно видимых ячеек и вставим вместо ненужных 29-ти только необходимые нам 5 строк.
Если есть подозрение, что подобную операцию вам придется проделывать часто, то имеет смысл добавить на панель инструментов Microsoft Excel кнопку для быстрого вызова такой функции. Это можно сделать через меню Сервис>Настройка (Tools> Customize), затем перейти на вкладку Команды (Commands), в категории Правка (Edit) найти кнопку Выделить видимые ячейки (Select visible cells) и перенести ее мышью на панель инструментов:
Превращение строк в столбцы и обратно
Простая операция, но если не знать как сделать ее правильно - можно потратить полдня на перетаскивание отдельных ячеек вручную:
На самом деле все просто. В той части высшей математики, которая описывает матрицы есть понятие транспонирования - действия, которое меняет строки и столбцы в матрице местами друг с другом. В Microsoft Excel это реализуется в три движения: Копируем таблицу
Щелкаем правой кнопкой мыши по пустой ячейке и выбираем команду Специальная вставка ( Paste Special)
В открывшемся окне ставим флаг Транспонировать ( Transpose) и жмем ОК:
Быстрое добавление данных в диаграмму
Представим себе простую ситуацию: у вас есть отчет за прошлый месяц с наглядной диаграммой. Задача - добавить в диаграмму новые числовые данные уже за этот месяц. Классический путь ее решения - это открыть окно источника данных для диаграммы, где добавить новый ряд данных, введя его имя и выделив диапазон с нужными данными. Причем зачастую сказать это легче, чем сделать - все зависит от сложности диаграммы.
Другой путь - простой, быстрый и красивый - выделить ячейки с новыми данными, скопировать их (CTRL+C) и вставить (CTRL+V) прямо в диаграмму. Excel 2003, в отличие от более поздних версий, поддерживает даже возможность перетаскивания выделенного диапазона ячеек с данными и забрасывания его прямо в диаграмму с помощью мыши!
Если хочется контролировать все нюансы и тонкости, то можно использовать не обычную, а специальную вставку, выбрав в меню Правка>Специальная вставка ( Edit> Paste Special). В этом случае Microsoft Excel отобразит диалоговое окно, позволяющее настроить куда и как именно будут добавлены новые данные:
Подобным же образом можно легко создать диаграмму, используя данные из разных таблиц с разных листов. На выполнение той же задачи классическим способом уйдет гораздо больше времени и сил.
Заполнение пустых ячеек
После выгрузки отчетов из некоторых программ в формат Excel или при создании сводных таблиц пользователи часто получают таблицы с пустыми ячейками в некоторых столбцах. Эти пропуски не позволяют применять к таблицами привычные и удобные инструменты типа автофильтра и сортировки. Естественным образом возникает необходимость заполнить пустоты значениями из вышестоящих ячеек:
Безусловно, при небольшом количестве данных, это легко можно сделать простым копированием - вручную протянув каждую заглавную ячейку в столбце А вниз на пустые ячейки. А если в таблице несколько сотен или тысяч строк и несколько десятков городов?
Есть способ решить эту задачу быстро и красиво при помощи одной формулы:
Выделите все ячейки в столбце с пустотами (т.е. диапазон A1:A12 в нашем случае)
Чтобы в выделении остались только пустые ячейки, нажмите клавишу F5 и в открывшемся окне переходов - кнопку Выделить. Увидите окно, позволяющее выбрать - какие именно ячейки мы хотим выделить:
Установите переключатель в положение Пустые ( Blank ) и нажмите ОК. Теперь в выделении должны остаться только пустые ячейки:
Не меняя выделения, т.е. не трогая мышь, введем формулу в первую выделенную ячейку (А2). Нажмите на клавиатуре на знак "равно" и затем на "стрелку вверх". Получим формулу, которая ссылается на предыдущую ячейку:
Чтобы ввести созданную формулу сразу во все выделенные пустые ячейки, нажмите не клавишу ENTER, а сочетание CTRL+ ENTER. Формула заполнит все пустые ячейки:
Теперь осталось только заменить формулы на значения для фиксации результатов. Выделите диапазон A1:A12, скопируйте его и вставьте в ячейки их значения, используя специальную вставку.
Выпадающий список в ячейке
Прием, который, без преувеличения, должен знать каждый, кто работает в Excel. Его применение способно улучшить, практически, любую таблицу вне зависимости от ее назначения. На всех тренингах я стараюсь показать его своим слушателям в первый же день.
Идея очень проста - во всех случаях, когда вы должны ввести данные из какого-либо набора, вместо ручного ввода в ячейку с клавиатуры выбирать нужное значение мышью из выпадающего списка:
Выбор товара из прайс-листа, имени клиента из клиентской базы, ФИО сотрудника из штатного расписания и т.д. Вариантов применения этой функции множество.
Чтобы создать выпадающий список в ячейке:
Выделите ячейки, в которых вы хотите создать выпадающий список.
Если у вас Excel 2003 или старше, то выберите в меню Данные>Проверка (Data>Validation). Если у вас Excel 2007/2010, то перейдите на вкладку Данные (Data) и нажмите кнопку Проверка данных (Data validation).
В открывшемся окне выберите вариант Список (List) из раскрывающегося списка.
В поле Источник (Source) надо указать значения, которые должны быть в списке. Тут возможны варианты:
Вписать в это поле текстовые варианты через точку с запятой
Если диапазон ячеек с исходными значениями находится на текущем листе - достаточно его просто выделить мышью.
Если он находится на другом листе этой книги, то ему придется заранее дать имя (выделить ячейки, нажать CTRL+F3, ввести имя диапазона без пробелов), а затем прописать это имя в поле Источник:
Также, именованный диапазон может быть динамическим, т.е. созданным на основе функций СМЕЩ ( OFFSET) и СЧЁТЗ ( COUNTA). Тогда при дописывании к нему новых данных, они будут автоматически в него включаться и отображаться в выпадающем списке.
На вкладках Сообщение для ввода (Input Message) и Сообщение об ошибке (Error Alert) можно ввести подсказку для пользователя, которая будет появляться при выделении ячейки ввода и ругательное сообщение, если пользователь вместо выбора одного из разрешенных вариантов упрямо попытается вписать свой текст напрямую с клавиатуры:
Заключение
Надеюсь, что хотя бы некоторые из вышеописанных приемов вы найдете для себя полезными и они облегчат вам ежедневную работу в Microsoft Excel.