Секреты эксель. Секреты Microsoft Exсel
Пользуетесь ли вы Excel? Мы выбрали 20 советов, которые помогут вам узнать его получше и оптимизировать свою работу с ним.
Выпустив Excel 2010, Microsoft чуть ли не удвоила функциональность этой программы, добавив множество улучшений и нововведений, многие из которых не сразу заметны. Неважно, опытный вы пользователь или новичок, найдется немало способов упростить работу с Excel. О некоторых из них мы сегодня расскажем.
Выделение всех ячеек одним кликом
Все ячейки можно выделить комбинацией клавиш Ctrl + A, которая, кстати, работает и во всех других программах. Однако есть более простой способ выделения. Нажав на кнопку в углу листа Excel, вы выделите все ячейки одним кликом.
Открытие нескольких файлов одновременно
Вместо того чтобы открывать каждый файл Excel по отдельности, их можно открыть вместе. Для этого выделите файлы, которые нужно открыть, и нажмите Enter.
Перемещение по файлам Excel
Когда у вас открыто несколько книг в Excel, между ними можно легко перемещаться с помощью комбинации клавиш Ctrl + Tab. Эта функция также доступна по всей системе Windows, и ее можно использовать во многих приложениях. К примеру, для переключения вкладок в браузере.
Добавление новых кнопок на панель быстрого доступа
Стандартно в панели быстрого доступа Excel находятся 3 кнопки. Вы можете изменить это количество и добавить те, которые нужны именно вам.
Перейдите в меню «Файл» ⇒ «Параметры» ⇒ «Панель быстрого доступа». Теперь можно выбрать любые кнопки, которые вам нужны.
Диагональная линия в ячейках
Иногда бывают ситуации, когда нужно добавить в таблицу диагональную линию. К примеру, чтобы разделить дату и время. Для этого на главной странице Excel нажмите на привычную иконку границ и выберите «Другие границы».
Добавление в таблицу пустых строк или столбцов
Вставить одну строку или столбец достаточно просто. Но что делать, если их нужно вставить гораздо больше? Выделите нужное количество строк или столбцов и нажмите «Вставить». После этого выберите место, куда нужно сдвинуться ячейкам, и вы получите нужное количество пустых строк.
Скоростное копирование и перемещение информации
Если вам нужно переместить любую информацию (ячейку, строку, столбец) в Excel, выделите ее и наведите мышку на границу, чтобы изменился указатель. После этого переместите информацию в то место, которое вам нужно. Если необходимо скопировать информацию, сделайте то же самое, но с зажатой клавишей Ctrl.
Быстрое удаление пустых ячеек
Пустые ячейки - это бич Excel. Иногда они появляются просто из ниоткуда. Чтобы избавиться от них всех за один раз, выделите нужный столбец, перейдите на вкладку «Данные» и нажмите «Фильтр». Над каждым столбцом появится стрелка, направленная вниз. Нажав на нее, вы попадете в меню, которое поможет избавиться от пустых полей.
Расширенный поиск
Нажав Ctrl + F, мы попадаем в меню поиска, с помощью которого можно искать любые данные в Excel. Однако его функциональность можно расширить, используя символы «?» и «*». Знак вопроса отвечает за один неизвестный символ, а астериск - за несколько. Их стоит использовать, если вы не уверены, как выглядит искомый запрос.
Если же вам нужно найти вопросительный знак или астериск и вы не хотите, чтобы вместо них Excel искал неизвестный символ, то поставьте перед ними «~».
Копирование уникальных записей
Уникальные записи могут быть полезными, если вам нужно выделить в таблице неповторяющуюся информацию. К примеру, по одному человеку каждого возраста. Для этого выберите нужный столбец и нажмите «Дополнительно» слева от пункта «Фильтр». Выберите исходный диапазон (откуда копировать) и диапазон, в который нужно поместить результат. Не забудьте поставить галочку.
Создание выборки
Если вы делаете опрос, в котором могут участвовать только мужчины от 19 до 60, вы легко можете создать подобную выборку с помощью Excel. Перейдите в пункт меню «Данные» ⇒ «Проверка данных» и выберите необходимый диапазон или другое условие. Вводя информацию, которая не подходит под это условие, пользователи будут получать сообщение, что информация неверна.
Быстрая навигация с помощью Ctrl и стрелки
Нажимая Ctrl + стрелка, можно перемещаться в крайние точки листа. К примеру, Ctrl + ⇓ перенесет курсор в нижнюю часть листа.
Транспонирование информации из столбца в строку
Довольно полезная функция, которая нужна не так уж и часто. Но если она вдруг понадобится, вряд ли вы будете транспонировать по одной. Для транспонирования в Excel есть специальная вставка.
Скопируйте диапазон ячеек, который нужно транспонировать. После этого кликните правой кнопкой на нужное место и выберите специальную вставку.
Как скрывать информацию в Excel
Не знаю, зачем это может пригодиться, но тем не менее такая функция в Excel есть. Выделите нужный диапазон ячеек, нажмите «Формат» ⇒ «Скрыть или отобразить» и выберите нужное действие.
Объединение текста с помощью «&»
Если вам нужно объединить текст из нескольких ячеек в одну, необязательно использовать сложные формулы. Достаточно выбрать ячейку, в которой будет соединен текст, нажать «=» и последовательно выбрать ячейки, ставя перед каждой символ «&».
Изменение регистра букв
С помощью определенных формул можно менять регистр всей текстовой информации в Excel. Функция «ПРОПИСН» делает все буквы прописными, а «СТРОЧН» - строчными. «ПРОПНАЧ» делает прописной только первую букву в каждом слове.
Внесение информации с нулями в начале
Если вы введете в Excel число 000356, то программа автоматически превратит его в 356. Если вы хотите оставить нули в начале, поставьте перед числом апостроф «’».
Ускорение ввода сложных слов
Если вы часто вводите одни и те же слова, то обрадуетесь, узнав, что в Excel есть автозамена. Она очень похожа на автозамену в смартфонах, поэтому вы сразу поймете, как ей пользоваться. С ее помощью можно заменить повторяющиеся конструкции аббревиатурами. К примеру, Екатерина Петрова - ЕП.
Больше информации
В правом нижнем углу можно следить за различной информацией. Однако мало кто знает, что, нажав туда правой кнопкой мыши, можно убрать ненужные и добавить нужные строки.
Переименование листа с помощью двойного клика
Это самый простой способ переименовать лист. Просто кликните по нему два раза левой кнопкой мыши и введите новое название.
Группировка данных
Когда вы подготавливаете каталог товаров с ценами, было бы неплохо побеспокоиться об удобстве его использования. Большое количество позиций на одном листе вынуждает использовать поиск, но что, если пользователь только делает выбор и не имеет представления о наименовании? В Интернет-каталогах проблема решается созданием групп товаров. Так почему бы и в книге Excel не сделать так же?
Организовать группировку достаточно просто. Выделите несколько строк и нажмите кнопку Группировать на вкладке Данные (см. рис. 1).
Рисунок 1 – Кнопка группировки
Затем укажите тип группировки – по строкам (см. рис. 2).
Рисунок 2 – Выбор типа группировки
В итоге мы получаем… не то, что нам нужно. Строки товаров объединились в группу, указанную под ними (см. рис. 3). В каталогах обычно сначала идёт заголовок, а потом содержимое.
Рисунок 3 – Группировка строк «вниз»
Это отнюдь не ошибка программы. Видимо, разработчики посчитали, что группировкой строк занимаются в основном составители финансовой отчётности, где итоговый результат выводится в конце блока.
Чтобы группировать строки «вверх» нужно изменить одну настройку. На вкладке Данные нажмите на маленькую стрелочку в нижнем правом углу раздела Структура (см. рис. 4).
Рисунок 4 – Кнопка, отвечающая за вывод окна настроек структуры
В открывшемся окне настроек снимите флажок с пункта Итоги в строках под данными (см. рис. 5) и нажмите кнопку ОК .
Рисунок 5 – Окно настроек структуры
Все группы, которые вы успели создать, автоматически изменятся на «верхний» тип. Разумеется, установленный параметр повлияет и на дальнейшее поведение программы. Однако снимать этот флажок вам придётся для каждого нового листа и каждой новой книги Excel, т.к. разработчики не предусмотрели «глобальной» установки типа группировки. Точно также нельзя использовать различные типы групп в пределах одной страницы.
После того, как вы распределили товары по категориям, можно собрать категории в более крупные разделы. Всего предусмотрено до девяти уровней группировки.
Неудобство при использовании данной функции заключается в необходимости нажимать кнопку ОК во всплывающем окошке, а собрать несвязанные диапазоны за один подход не удастся.
Рисунок 6 – Многоуровневая структура каталога в Excel
Теперь вы сможете раскрывать и закрывать части каталога, щёлкая по плюсам и минусам в левой колонке (см. рис. 6). Чтобы развернуть весь уровень, нажмите на одну из цифр в верхней части.
Чтобы вывести строки на более высокий уровень иерархии, воспользуйтесь кнопкой Разгруппировать вкладки Данные . Полностью избавиться от группировки можно при помощи пункта меню Удалить структуру (см. рис. 7). Будьте внимательны, отменить действие невозможно!
Рисунок 7 – Снимаем группировку строк
Закрепление областей листа
Достаточно часто при работе с таблицами Excel возникает необходимость закрепить некоторые области листа. Там могут располагаться, например, заголовки строк / столбцов, логотип компании или другая информация.
Если вы закрепляете первую строку или первый столбец, то всё очень просто. Откройте вкладку Вид и в раскрывающемся меню Закрепить области выберите соответственно пункты Закрепить верхнюю строку или Закрепить первый столбец (см. рис. 8). Однако одновременно и строку, и столбец таким образом «заморозить» не удастся.
Рисунок 8 – Закрепляем строку или столбец
Чтобы снять закрепление, выберите в том же меню пункт Снять закрепление областей (пункт заменяет собой строку Закрепить области , если на странице применена «заморозка»).
А вот закрепление нескольких строк или области из строк и столбцов осуществляется не так прозрачно. Вы выделяете три строки, щёлкаете по пункту Закрепить области , и… Excel «замораживает» только две. Почему так? Возможен ещё более плохой вариант, когда области закрепляются непредсказуемым образом (например, вы выделяете две строки, а программа ставит границы после пятнадцатой). Но не будем списывать это на недосмотр разработчиков, потому что единственный правильный вариант использования данной функции выглядит по-другому.
Вам нужно щёлкнуть мышью по ячейке ниже строк, которые вы хотите закрепить, и, соответственно, правее закрепляемых столбцов, а уже потом выбрать пункт Закрепить области . Пример: на рисунке 9 выделена ячейка B 4 . Значит, закрепляться будут три строки и первый столбец, которые останутся на своих местах при прокрутке листа как по горизонтали, так и по вертикали.
Рисунок 9 – Закрепляем область из строк и столбцов
Вы можете применить фоновую заливку для закреплённых областей, чтобы указать пользователю на особенное поведение данных ячеек.
Поворот листа (замена строк на столбцы и наоборот)
Представьте такую ситуацию: вы несколько часов работали над набором таблицы в Excel и вдруг поняли, что неверно спроектировали структуру – заголовки столбцов следовало бы расписать по строкам или строки по столбцам (это не имеет значения). Набирать всё вручную заново? Ни за что! В Excel предусмотрена функция, позволяющая осуществить «поворот» листа на 90 градусов, переместив таким образом содержимое строк в столбцы.
Рисунок 10 – Исходная таблица
Итак, мы имеем некоторую таблицу, которую нужно «повернуть» (см. рис. 10).
- Выделяем ячейки с данными. Выделяются именно ячейки, а не строки и столбцы, иначе ничего не получится.
- Копируем их в буфер обмена сочетанием клавиш
или любым другим способом. - Переходим к пустому листу или свободному пространству текущего листа. Важное замечание: вставлять поверх текущих данных нельзя!
- Вставляем данные комбинацией клавиш
и в меню параметров вставки выбираем вариант Транспонировать (см. рис. 11). Как вариант, вы можете использовать меню Вставить со вкладки Главная (см. рис. 12).
Рисунок 11 – Вставка с транспонированием
Рисунок 12 – Транспонирование из главного меню
Вот и всё, поворот таблицы выполнен (см. рис. 13). При этом форматирование сохраняется, а формулы изменяются в соответствии с новым положением ячеек – никакой рутинной работы не потребуется.
Рисунок 13 – Результат после поворота
Показ формул
Иногда возникает ситуация, когда вы не можете найти нужную формулу среди большого количества ячеек, либо просто не знаете, что и где следует искать. В этом случае вам пригодится возможность вывести на лист не результат вычислений, а исходные формулы.
Нажмите кнопку Показать формулы на вкладке Формулы (см. рис. 14), чтобы изменить представление данных на листе (см. рис. 15).
Рисунок 14 – Кнопка «Показать формулы»
Рисунок 15 – Теперь на листе видны формулы, а не результаты расчёта
Если вам трудно сориентироваться по адресам ячеек, отображаемым в строке формул, нажмите кнопку Влияющие ячейки со вкладки Формулы (см. рис. 14). Зависимости будут показаны стрелками (см. рис. 16). Для использования этой функции сначала следует выделить одну ячейку.
Рисунок 16 – Зависимости ячейки показаны стрелками
Скрываются зависимости нажатием кнопки Убрать стрелки .
Перенос строк в ячейках
Достаточно часто в книгах Excel встречаются длинные надписи, не помещающиеся в ячейку по ширине (см. рис. 17). Можно, конечно, раздвинуть столбец, но не всегда этот вариант приемлем.
Рисунок 17 – Надписи не помещаются в ячейки
Выделите ячейки с длинными надписями и нажмите кнопку Перенос текста на Главной вкладке (см. рис. 18), чтобы перейти к многострочному отображению (см. рис. 19).
Рисунок 18 – Кнопка «Перенос текста»
Рисунок 19 – Многострочное отображение текста
Поворот текста в ячейке
Наверняка вы сталкивались с ситуацией, когда текст в ячейках нужно было разместить не по горизонтали, а по вертикали. Например, чтобы подписать группу строк или узкие столбцы. В Excel 2010 имеются средства, позволяющие повернуть текст в ячейках.
В зависимости от своих предпочтений вы можете пойти двумя путями:
- Сначала создать надпись, а потом её повернуть.
- Настроить поворот надписи в ячейке, а потом ввести текст.
Варианты различаются незначительно, поэтому рассмотрим только один из них. Для начала я объединил шесть строк в одну при помощи кнопки Объединить и поместить в центре на Главной вкладке (см. рис. 20) и ввёл обобщающую надпись (см. рис. 21).
Рисунок 20 – Кнопка объединения ячеек
Рисунок 21 – Сначала создаём горизонтальную подпись
Рисунок 22 – Кнопка поворота текста
Можно дополнительно уменьшить ширину столбца (см. рис. 23). Готово!
Рисунок 23 – Вертикальный текст ячейки
Если есть такое желание, угол поворота текста вы можете задать вручную. В том же списке (см. рис. 22) выберите пункт Формат выравнивания ячейки и в открывшемся окошке задайте произвольный угол и выравнивание (см. рис. 24).
Рисунок 24 – Задаём произвольный угол поворота текста
Форматирование ячеек по условию
Возможности условного форматирования появились в Excel уже давно, но к версии 2010 года были значительно усовершенствованы. Возможно, вам даже не придётся разбираться в тонкостях создания правил, т.к. разработчики предусмотрели множество заготовок. Давайте посмотрим, как пользоваться условным форматированием в Excel 2010.
Первое, что нужно сделать – выделить ячейки. Далее, на Главной вкладке нажмите кнопку Условное форматирование и выберите одну из заготовок (см. рис. 25). Результат будет отображаться на листе сразу, поэтому вам не придётся долго перебирать варианты.
Рисунок 25 – Выбираем заготовку условного форматирования
Гистограммы выглядят достаточно интересно и хорошо отражают суть информации о цене – чем она выше, тем длиннее отрезок.
Цветовые шкалы и наборы значков можно использовать для индикации различных состояний, например, переходы от критических затрат к допустимым (см. рис. 26).
Рисунок 26 – Цветовая шкала от красного к зелёному с промежуточным жёлтым
Вы можете комбинировать гистограммы, шкалы и значки в одном диапазоне ячеек. Например, гистограммы и значки на рисунке 27 показывают допустимую и чрезмерно низкую производительность устройств.
Рисунок 27 – Гистограмма и набор значков отражают производительность некоторых условных устройств
Чтобы удалить условное форматирование ячеек, выделите их и в меню условного форматирования выберите пункт Удалить правила из выделенных ячеек (см. рис. 28).
Рисунок 28 – Удаляем правила условного форматирования
Excel 2010 использует заготовки для быстрого доступа к возможностям условного форматирования, т.к. настройка собственных правил для большинства людей является далеко не очевидной. Однако, если предусмотренные разработчиками шаблоны вас не устраивают, вы можете создать свои правила оформления ячеек по различных условиям. Полное описание данного функционала выходит за рамки текущей статьи.
Использование фильтров
Фильтры позволяют быстро находить нужную информацию в большой таблице и представлять её в компактном виде. Например, из длинного списка книг можно выбрать произведения Гоголя, а из прайс-листа компьютерного магазина – процессоры Intel.
Как и большинство других операций, фильтр требует выделения ячеек. Однако выделять всю таблицу с данными не потребуется, достаточно отметить строки над нужными столбцами данных. Это значительно увеличивает удобство от использования фильтров.
После того, как ячейки выделены, на вкладке Главная нажмите кнопку Сортировка и фильтр и выберите пункт Фильтр (см. рис. 29).
Рисунок 29 – Создаём фильтры
Теперь ячейки преобразятся в раскрывающиеся списки, где вы можете задать параметры выборки. Например, мы ищем все упоминания про Intel в столбце Наименование товара . Для этого выбираем текстовый фильтр Содержит (см. рис. 30).
Рисунок 30 – Создаём текстовый фильтр
Рисунок 31 – Создаём фильтр по слову
Впрочем, гораздо быстрее добиться того же эффекта, вписав слово в поле Поиск контекстного меню, показанного на рисунке 30. Зачем тогда вызывать дополнительное окно? Оно пригодится в том случае, если вы хотите указать несколько условий выборки или выбрать другие параметры фильтрации (не содержит , начинается с…, заканчивается на… ).
Для числовых данных доступны другие параметры (см. рис. 32). Например, можно выбрать 10 наибольших или 7 наименьших значений (количество настраивается).
Рисунок 32 – Числовые фильтры
Фильтры Excel предоставляют достаточно богатые возможности, сравнимые с выборкой запросом SELECT в системах управления базами данных (СУБД).
Отображение информационных кривых
Информационные кривые (инфокривые) – нововведение в Excel 2010. Эта функция позволяет отобразить динамику изменения числовых параметров прямо в ячейке, не прибегая к построению диаграммы. Изменения в числах будут сразу же показаны на микрографике.
Рисунок 33 – Инфокривая Excel 2010
Чтобы создать инфокривую, нажмите на одну из кнопок в блоке Инфокривые на вкладке Вставка (см. рис. 34), а затем задайте диапазон ячеек для построения.
Рисунок 34 – Вставка инфокривой
Как и диаграммы, информационные кривые имеют множество параметров для настройки. Более подробное руководство по использованию данного функционала описано в статье .
Заключение
В статье были рассмотрены некоторые полезные возможности Excel 2010, ускоряющие работу, улучшающие внешний вид таблиц или удобство использования. При этом не имеет значения, создаёте ли вы файл сами или используете чужой – в Excel 2010 найдутся функции для всех пользователей.
Группировка данных
Когда вы подготавливаете каталог товаров с ценами, было бы неплохо побеспокоиться об удобстве его использования. Большое количество позиций на одном листе вынуждает использовать поиск, но что, если пользователь только делает выбор и не имеет представления о наименовании? В Интернет-каталогах проблема решается созданием групп товаров. Так почему бы и в книге Excel не сделать так же?
Организовать группировку достаточно просто. Выделите несколько строк и нажмите кнопку Группировать на вкладке Данные (см. рис. 1).
Рисунок 1 – Кнопка группировки
Затем укажите тип группировки – по строкам (см. рис. 2).
Рисунок 2 – Выбор типа группировки
В итоге мы получаем… не то, что нам нужно. Строки товаров объединились в группу, указанную под ними (см. рис. 3). В каталогах обычно сначала идёт заголовок, а потом содержимое.
Рисунок 3 – Группировка строк «вниз»
Это отнюдь не ошибка программы. Видимо, разработчики посчитали, что группировкой строк занимаются в основном составители финансовой отчётности, где итоговый результат выводится в конце блока.
Чтобы группировать строки «вверх» нужно изменить одну настройку. На вкладке Данные нажмите на маленькую стрелочку в нижнем правом углу раздела Структура (см. рис. 4).
Рисунок 4 – Кнопка, отвечающая за вывод окна настроек структуры
В открывшемся окне настроек снимите флажок с пункта Итоги в строках под данными (см. рис. 5) и нажмите кнопку ОК .
Рисунок 5 – Окно настроек структуры
Все группы, которые вы успели создать, автоматически изменятся на «верхний» тип. Разумеется, установленный параметр повлияет и на дальнейшее поведение программы. Однако снимать этот флажок вам придётся для каждого нового листа и каждой новой книги Excel, т.к. разработчики не предусмотрели «глобальной» установки типа группировки. Точно также нельзя использовать различные типы групп в пределах одной страницы.
После того, как вы распределили товары по категориям, можно собрать категории в более крупные разделы. Всего предусмотрено до девяти уровней группировки.
Неудобство при использовании данной функции заключается в необходимости нажимать кнопку ОК во всплывающем окошке, а собрать несвязанные диапазоны за один подход не удастся.
Рисунок 6 – Многоуровневая структура каталога в Excel
Теперь вы сможете раскрывать и закрывать части каталога, щёлкая по плюсам и минусам в левой колонке (см. рис. 6). Чтобы развернуть весь уровень, нажмите на одну из цифр в верхней части.
Чтобы вывести строки на более высокий уровень иерархии, воспользуйтесь кнопкой Разгруппировать вкладки Данные . Полностью избавиться от группировки можно при помощи пункта меню Удалить структуру (см. рис. 7). Будьте внимательны, отменить действие невозможно!
Рисунок 7 – Снимаем группировку строк
Закрепление областей листа
Достаточно часто при работе с таблицами Excel возникает необходимость закрепить некоторые области листа. Там могут располагаться, например, заголовки строк / столбцов, логотип компании или другая информация.
Если вы закрепляете первую строку или первый столбец, то всё очень просто. Откройте вкладку Вид и в раскрывающемся меню Закрепить области выберите соответственно пункты Закрепить верхнюю строку или Закрепить первый столбец (см. рис. 8). Однако одновременно и строку, и столбец таким образом «заморозить» не удастся.
Рисунок 8 – Закрепляем строку или столбец
Чтобы снять закрепление, выберите в том же меню пункт Снять закрепление областей (пункт заменяет собой строку Закрепить области , если на странице применена «заморозка»).
А вот закрепление нескольких строк или области из строк и столбцов осуществляется не так прозрачно. Вы выделяете три строки, щёлкаете по пункту Закрепить области , и… Excel «замораживает» только две. Почему так? Возможен ещё более плохой вариант, когда области закрепляются непредсказуемым образом (например, вы выделяете две строки, а программа ставит границы после пятнадцатой). Но не будем списывать это на недосмотр разработчиков, потому что единственный правильный вариант использования данной функции выглядит по-другому.
Вам нужно щёлкнуть мышью по ячейке ниже строк, которые вы хотите закрепить, и, соответственно, правее закрепляемых столбцов, а уже потом выбрать пункт Закрепить области . Пример: на рисунке 9 выделена ячейка B 4 . Значит, закрепляться будут три строки и первый столбец, которые останутся на своих местах при прокрутке листа как по горизонтали, так и по вертикали.
Рисунок 9 – Закрепляем область из строк и столбцов
Вы можете применить фоновую заливку для закреплённых областей, чтобы указать пользователю на особенное поведение данных ячеек.
Поворот листа (замена строк на столбцы и наоборот)
Представьте такую ситуацию: вы несколько часов работали над набором таблицы в Excel и вдруг поняли, что неверно спроектировали структуру – заголовки столбцов следовало бы расписать по строкам или строки по столбцам (это не имеет значения). Набирать всё вручную заново? Ни за что! В Excel предусмотрена функция, позволяющая осуществить «поворот» листа на 90 градусов, переместив таким образом содержимое строк в столбцы.
Рисунок 10 – Исходная таблица
Итак, мы имеем некоторую таблицу, которую нужно «повернуть» (см. рис. 10).
- Выделяем ячейки с данными. Выделяются именно ячейки, а не строки и столбцы, иначе ничего не получится.
- Копируем их в буфер обмена сочетанием клавиш
или любым другим способом. - Переходим к пустому листу или свободному пространству текущего листа. Важное замечание: вставлять поверх текущих данных нельзя!
- Вставляем данные комбинацией клавиш
и в меню параметров вставки выбираем вариант Транспонировать (см. рис. 11). Как вариант, вы можете использовать меню Вставить со вкладки Главная (см. рис. 12).
Рисунок 11 – Вставка с транспонированием
Рисунок 12 – Транспонирование из главного меню
Вот и всё, поворот таблицы выполнен (см. рис. 13). При этом форматирование сохраняется, а формулы изменяются в соответствии с новым положением ячеек – никакой рутинной работы не потребуется.
Рисунок 13 – Результат после поворота
Показ формул
Иногда возникает ситуация, когда вы не можете найти нужную формулу среди большого количества ячеек, либо просто не знаете, что и где следует искать. В этом случае вам пригодится возможность вывести на лист не результат вычислений, а исходные формулы.
Нажмите кнопку Показать формулы на вкладке Формулы (см. рис. 14), чтобы изменить представление данных на листе (см. рис. 15).
Рисунок 14 – Кнопка «Показать формулы»
Рисунок 15 – Теперь на листе видны формулы, а не результаты расчёта
Если вам трудно сориентироваться по адресам ячеек, отображаемым в строке формул, нажмите кнопку Влияющие ячейки со вкладки Формулы (см. рис. 14). Зависимости будут показаны стрелками (см. рис. 16). Для использования этой функции сначала следует выделить одну ячейку.
Рисунок 16 – Зависимости ячейки показаны стрелками
Скрываются зависимости нажатием кнопки Убрать стрелки .
Перенос строк в ячейках
Достаточно часто в книгах Excel встречаются длинные надписи, не помещающиеся в ячейку по ширине (см. рис. 17). Можно, конечно, раздвинуть столбец, но не всегда этот вариант приемлем.
Рисунок 17 – Надписи не помещаются в ячейки
Выделите ячейки с длинными надписями и нажмите кнопку Перенос текста на Главной вкладке (см. рис. 18), чтобы перейти к многострочному отображению (см. рис. 19).
Рисунок 18 – Кнопка «Перенос текста»
Рисунок 19 – Многострочное отображение текста
Поворот текста в ячейке
Наверняка вы сталкивались с ситуацией, когда текст в ячейках нужно было разместить не по горизонтали, а по вертикали. Например, чтобы подписать группу строк или узкие столбцы. В Excel 2010 имеются средства, позволяющие повернуть текст в ячейках.
В зависимости от своих предпочтений вы можете пойти двумя путями:
- Сначала создать надпись, а потом её повернуть.
- Настроить поворот надписи в ячейке, а потом ввести текст.
Варианты различаются незначительно, поэтому рассмотрим только один из них. Для начала я объединил шесть строк в одну при помощи кнопки Объединить и поместить в центре на Главной вкладке (см. рис. 20) и ввёл обобщающую надпись (см. рис. 21).
Рисунок 20 – Кнопка объединения ячеек
Рисунок 21 – Сначала создаём горизонтальную подпись
Рисунок 22 – Кнопка поворота текста
Можно дополнительно уменьшить ширину столбца (см. рис. 23). Готово!
Рисунок 23 – Вертикальный текст ячейки
Если есть такое желание, угол поворота текста вы можете задать вручную. В том же списке (см. рис. 22) выберите пункт Формат выравнивания ячейки и в открывшемся окошке задайте произвольный угол и выравнивание (см. рис. 24).
Рисунок 24 – Задаём произвольный угол поворота текста
Форматирование ячеек по условию
Возможности условного форматирования появились в Excel уже давно, но к версии 2010 года были значительно усовершенствованы. Возможно, вам даже не придётся разбираться в тонкостях создания правил, т.к. разработчики предусмотрели множество заготовок. Давайте посмотрим, как пользоваться условным форматированием в Excel 2010.
Первое, что нужно сделать – выделить ячейки. Далее, на Главной вкладке нажмите кнопку Условное форматирование и выберите одну из заготовок (см. рис. 25). Результат будет отображаться на листе сразу, поэтому вам не придётся долго перебирать варианты.
Рисунок 25 – Выбираем заготовку условного форматирования
Гистограммы выглядят достаточно интересно и хорошо отражают суть информации о цене – чем она выше, тем длиннее отрезок.
Цветовые шкалы и наборы значков можно использовать для индикации различных состояний, например, переходы от критических затрат к допустимым (см. рис. 26).
Рисунок 26 – Цветовая шкала от красного к зелёному с промежуточным жёлтым
Вы можете комбинировать гистограммы, шкалы и значки в одном диапазоне ячеек. Например, гистограммы и значки на рисунке 27 показывают допустимую и чрезмерно низкую производительность устройств.
Рисунок 27 – Гистограмма и набор значков отражают производительность некоторых условных устройств
Чтобы удалить условное форматирование ячеек, выделите их и в меню условного форматирования выберите пункт Удалить правила из выделенных ячеек (см. рис. 28).
Рисунок 28 – Удаляем правила условного форматирования
Excel 2010 использует заготовки для быстрого доступа к возможностям условного форматирования, т.к. настройка собственных правил для большинства людей является далеко не очевидной. Однако, если предусмотренные разработчиками шаблоны вас не устраивают, вы можете создать свои правила оформления ячеек по различных условиям. Полное описание данного функционала выходит за рамки текущей статьи.
Использование фильтров
Фильтры позволяют быстро находить нужную информацию в большой таблице и представлять её в компактном виде. Например, из длинного списка книг можно выбрать произведения Гоголя, а из прайс-листа компьютерного магазина – процессоры Intel.
Как и большинство других операций, фильтр требует выделения ячеек. Однако выделять всю таблицу с данными не потребуется, достаточно отметить строки над нужными столбцами данных. Это значительно увеличивает удобство от использования фильтров.
После того, как ячейки выделены, на вкладке Главная нажмите кнопку Сортировка и фильтр и выберите пункт Фильтр (см. рис. 29).
Рисунок 29 – Создаём фильтры
Теперь ячейки преобразятся в раскрывающиеся списки, где вы можете задать параметры выборки. Например, мы ищем все упоминания про Intel в столбце Наименование товара . Для этого выбираем текстовый фильтр Содержит (см. рис. 30).
Рисунок 30 – Создаём текстовый фильтр
Рисунок 31 – Создаём фильтр по слову
Впрочем, гораздо быстрее добиться того же эффекта, вписав слово в поле Поиск контекстного меню, показанного на рисунке 30. Зачем тогда вызывать дополнительное окно? Оно пригодится в том случае, если вы хотите указать несколько условий выборки или выбрать другие параметры фильтрации (не содержит , начинается с…, заканчивается на… ).
Для числовых данных доступны другие параметры (см. рис. 32). Например, можно выбрать 10 наибольших или 7 наименьших значений (количество настраивается).
Рисунок 32 – Числовые фильтры
Фильтры Excel предоставляют достаточно богатые возможности, сравнимые с выборкой запросом SELECT в системах управления базами данных (СУБД).
Отображение информационных кривых
Информационные кривые (инфокривые) – нововведение в Excel 2010. Эта функция позволяет отобразить динамику изменения числовых параметров прямо в ячейке, не прибегая к построению диаграммы. Изменения в числах будут сразу же показаны на микрографике.
Рисунок 33 – Инфокривая Excel 2010
Чтобы создать инфокривую, нажмите на одну из кнопок в блоке Инфокривые на вкладке Вставка (см. рис. 34), а затем задайте диапазон ячеек для построения.
Рисунок 34 – Вставка инфокривой
Как и диаграммы, информационные кривые имеют множество параметров для настройки. Более подробное руководство по использованию данного функционала описано в статье .
Заключение
В статье были рассмотрены некоторые полезные возможности Excel 2010, ускоряющие работу, улучшающие внешний вид таблиц или удобство использования. При этом не имеет значения, создаёте ли вы файл сами или используете чужой – в Excel 2010 найдутся функции для всех пользователей.
Прогресс не стоит на месте, и это высказывание актуально и для разработок . Новые версии популярного офисного пакета каждый раз совершенствуются, добавляя функциональности и эргономики во внешний вид.
Одним из мощных инструментов MicrosoftOffice, конечно, считается Excel– электронные таблицы. Зачастую, их потенциал остаётся раскрытым на половину, что приводит к трудностям решения расчётов, сводя всё к рутинной работе. Чтобы Вам было немного легче осваивать этот продукт, предлагаю 20 простеньких советов по работе, :
1. Как открыть несколько электронных таблиц за раз?
Чтобы не открывать каждый файл Excel в отдельности, а в одно действие запустить просмотр перечень требуемых таблиц, выделите ярлыки файлов с помощью курсора и зажатой клавиши «Ctrl» и нажмите «Enter».
2. Как переименовать лист по двойному клику?
Здесь работает тот же принцип что и в системе Windows, когда Вы два раза кликаете по объекту, он становится доступным для переименования (). В Excel также, если два раза щёлкнуть левой кнопкой мыши по названию листа, его можно будет переименовать.
3. Как скопировать уникальные записи?
Такая возможность может быть очень полезной, особенно, когда требуется выделить уникальные записи одного свойства. Например, в столбце «Вес товара» выделить каждое неповторяющиеся цифровое значение. Для этого выберите нужный столбец и нажмите «Дополнительно» слева от пункта «Фильтр». Выберите исходный диапазон (откуда копировать) и диапазон, в который нужно поместить результат. Не забудьте поставить галочку.
4. Как быстро перемещаться по открытым файлам?
Если Вы работаете сразу с несколькими открытыми документами (книгами Excel), то чтобы перейти на следующую нажмите сочетание клавиш «Ctrl + Tab», а чтобы перемещаться по всем книгам, удерживая клавишу «Ctrl», нажимайте «Tab» — пока не достигнете искомой таблицы. Такой принцип присущ для большинства программ и самой операционной системы, например, при перемещении по вкладкам активного окна.
5. Как добавить новые кнопки на панель «быстрого доступа»?
По умолчанию панель «быстрого доступа Excel» Предоставляет три кнопки, это можно исправить и добавить индивидуальный набор функций.
Переходим по пути: Файл — Параметры — Панель быстрого доступа. И выбираем требуемые кнопки для панели, не забывая сохранить изменения.
6. Как добавить диагональное деление ячейки?
Для этого на главной странице Excel нажмите на привычную иконку границ и выберите «Другие границы». Полезная возможность, когда требуется поделить название ячейки или столбца (дата и время).
7. Как добавить в таблицу пустые строки или столбцы?
Добавление одинарной строчки или столбца не вызывает сложностей, а вот что сделать для вставки нескольких пустых строк или столбцов? Выделите нужное количество строк или столбцов и нажмите «Вставить». После укажите место куда должны сместиться ячейки, и у Вас появятся пустые строки.
8. Как быстро скопировать и переместить информацию?
Если Вам понадобится переместить значение одной ячейки в другую, то выделите содержимое первой – двойным кликом и, удерживая левую кнопку мышки, перетяните во вторую. Для того чтобы скопировать значение, проделайте тоже самое, но с зажатой клавишей «Ctrl».
9. Как быстро удалить пустые ячейки?
Нередко электронные таблицы без вашего участия могут создавать пустые ячейки, которые неуместны в ваших расчётах. Чтобы исключить их выделите нужный столбец, перейдите на вкладку «Данные» и нажмите «Фильтр». Над каждым столбцом появится стрелка, направленная вниз. Нажав на нее, попадаем в меню, которое поможет избавиться от пустых ячеек.
10. Как получать больше информации?
Многие пользователи Excel, замечали в правом нижнем углу отображение некоторого количества информации. Но не каждый знает, что нажав правой кнопкой мышки, можно настроить эту область (расширить информацию или минимизировать).
11. Как выделить все ячейки одним кликом?
Практически в любом приложении, для выделения всего содержимого, достаточно, воспользоваться сочетанием «Ctrl + A», но в программе Excel помимо этого есть кнопка в углу листа, нажав вы выделите все ячейки одним кликом.
12. Как воспользоваться расширенным поиском?
Во многих программах горячие клавиши «Ctrl + F» вызывают функцию поиска, электронные таблицы не стали исключением. Но поиск можно расширить путём добавления в запрос символов: «?» и «*».Знак вопроса поможет Вам найти по запросу с неизвестным символом, когда по каким – то причинам полное наименование неизвестно. Знак звёздочка будет полезен для поиска с несколькими неизвестными, например, если Вы располагаете только началом значения ячейки.
Если в поисковом запросе требуется найти знаки вопроса или звёздочки, то перед символом укажите: «~».
13. Как воспользоваться навигацией по «Ctrl» и клавиш стрелок?
Воспользовавшись навигационными клавишами клавиатуры (стрелки). При зажатой «Ctrl» можно перемещаться в крайние точки таблицы. Например, зажав клавишу «Ctrl» и клавишу вниз – курсор переместится в крайнее нижнее положение.
14. Как транспонировать информацию столбца в строку?
Редкая потребность в работе с таблицами, но может оказаться актуальна, и тут придётся либо переносить по одной ячейки, либо применить функцию Excel.
Скопируем диапазон ячеек, который нужно транспонировать. После этого щёлкните правой кнопкой на нужное место и выбираем «специальную вставку».
15. Как скрывать информацию в Excel?
Немного странная возможность, которая имеет место быть. Вряд ли может пригодится при банальных расчётах, но если выделить определённые ячейки, а после нажать «Формат» — можно выбрать «Скрыть или показать».
16. Как объединить текст по средствам «&»?
Для того чтобы объединить несколько значений, не нужно применять сложных формул. Достаточно, в целевом поле ввести знак равенства, а перед расчётными ячейками поставить знак «&».
17. Как изменить регистр букв?
С помощью специальных формул можно, не только , но и менять регистр всего текста в Excel. Функция «ПРОПИСН» делает все буквы прописными, а «СТРОЧН» - строчными. «ПРОПНАЧ» делает прописной только первую букву в каждом слове.
18. Как ускорить ввод сложных слов?
Если одни и те же данные приходится, достаточно, часто вводить, то функция «Автозамена» в Excel поможет избежать рутинного письма. Здесь работает, многим известный, принцип ввода на смартфонах, например, можно научить программу писать вместо Андрей Евгеньевич – только аббревиатуру «А. Е.» (и наоборот).
19. Как создать выборку?
Если вы делаете соц.опрос, в котором могут участвовать только женщины возрастной категории от 18 до 55 лет. Перейдите в пункт меню «Данные» ? «Проверка данных» и выберите необходимый диапазон или другое условие. Вводя информацию, которая не подходит под это условие, пользователи будут получать сообщение, что информация неверна.
20. Как оставить нули в начале чисел?
Вы могли заметить, что Excel не даёт вносить цифровые значения типа «00000631», вместо этого он обрезает до «631», исключая нули в начале. Чтобы исправить это, перед нулями поставьте знак «’».
Вместо послесловия
Вот такая подборка практических рекомендаций по работе в MicrosoftExcel. Ничего секретного вышеописанное не содержит, но благодаря этому можно, как минимум, узнать об электронных таблицах немного больше, а, как максимум, оптимизировать свою работу!
Будем рады, если дополните своими «секретами Excel» в комментарии!
ВКонтакте
Выпустив Excel 2010, Microsoft чуть ли не удвоила функциональность этой программы, добавив множество улучшений и нововведений, многие из которых не сразу заметны. Неважно, опытный вы пользователь или новичок, найдется немало способов упростить работу с Excel. О некоторых из них мы сегодня расскажем.
Выделение всех ячеек одним кликом
Все ячейки можно выделить комбинацией клавиш Ctrl + A, которая, кстати, работает и во всех других программах. Однако есть более простой способ выделения. Нажав на кнопку в углу листа Excel, вы выделите все ячейки одним кликом.
Открытие нескольких файлов одновременно
Вместо того чтобы открывать каждый файл Excel по отдельности, их можно открыть вместе. Для этого выделите файлы, которые нужно открыть, и нажмите Enter.
Перемещение по файлам Excel
Когда у вас открыто несколько книг в Excel, между ними можно легко перемещаться с помощью комбинации клавиш Ctrl + Tab. Эта функция также доступна по всей системе Windows, и ее можно использовать во многих приложениях. К примеру, для переключения вкладок в браузере.
Добавление новых кнопок на панель быстрого доступа
Стандартно в панели быстрого доступа Excel находятся 3 кнопки. Вы можете изменить это количество и добавить те, которые нужны именно вам.
Перейдите в меню «Файл» ⇒ «Параметры» ⇒ «Панель быстрого доступа». Теперь можно выбрать любые кнопки, которые вам нужны.
Диагональная линия в ячейках
Иногда бывают ситуации, когда нужно добавить в таблицу диагональную линию. К примеру, чтобы разделить дату и время. Для этого на главной странице Excel нажмите на привычную иконку границ и выберите «Другие границы».
Добавление в таблицу пустых строк или столбцов
Вставить одну строку или столбец достаточно просто. Но что делать, если их нужно вставить гораздо больше? Выделите нужное количество строк или столбцов и нажмите «Вставить». После этого выберите место, куда нужно сдвинуться ячейкам, и вы получите нужное количество пустых строк.
Скоростное копирование и перемещение информации
Если вам нужно переместить любую информацию (ячейку, строку, столбец) в Excel, выделите ее и наведите мышку на границу, чтобы изменился указатель. После этого переместите информацию в то место, которое вам нужно. Если необходимо скопировать информацию, сделайте то же самое, но с зажатой клавишей Ctrl.
Быстрое удаление пустых ячеек
Пустые ячейки - это бич Excel. Иногда они появляются просто из ниоткуда. Чтобы избавиться от них всех за один раз, выделите нужный столбец, перейдите на вкладку «Данные» и нажмите «Фильтр». Над каждым столбцом появится стрелка, направленная вниз. Нажав на нее, вы попадете в меню, которое поможет избавиться от пустых полей.
Расширенный поиск
Нажав Ctrl + F, мы попадаем в меню поиска, с помощью которого можно искать любые данные в Excel. Однако его функциональность можно расширить, используя символы «?» и «*». Знак вопроса отвечает за один неизвестный символ, а астериск - за несколько. Их стоит использовать, если вы не уверены, как выглядит искомый запрос.
Если же вам нужно найти вопросительный знак или астериск и вы не хотите, чтобы вместо них Excel искал неизвестный символ, то поставьте перед ними «~».
Копирование уникальных записей
Уникальные записи могут быть полезными, если вам нужно выделить в таблице неповторяющуюся информацию. К примеру, по одному человеку каждого возраста. Для этого выберите нужный столбец и нажмите «Дополнительно» слева от пункта «Фильтр». Выберите исходный диапазон (откуда копировать) и диапазон, в который нужно поместить результат. Не забудьте поставить галочку.
Создание выборки
Если вы делаете опрос, в котором могут участвовать только мужчины от 19 до 60, вы легко можете создать подобную выборку с помощью Excel. Перейдите в пункт меню «Данные» ⇒ «Проверка данных» и выберите необходимый диапазон или другое условие. Вводя информацию, которая не подходит под это условие, пользователи будут получать сообщение, что информация неверна.
Быстрая навигация с помощью Ctrl и стрелки
Нажимая Ctrl + стрелка, можно перемещаться в крайние точки листа. К примеру, Ctrl + ⇓ перенесет курсор в нижнюю часть листа.
Транспонирование информации из столбца в строку
Довольно полезная функция, которая нужна не так уж и часто. Но если она вдруг понадобится, вряд ли вы будете транспонировать по одной. Для транспонирования в Excel есть специальная вставка.
Скопируйте диапазон ячеек, который нужно транспонировать. После этого кликните правой кнопкой на нужное место и выберите специальную вставку.
Как скрывать информацию в Excel
Не знаю, зачем это может пригодиться, но тем не менее такая функция в Excel есть. Выделите нужный диапазон ячеек, нажмите «Формат» ⇒ «Скрыть или отобразить» и выберите нужное действие.
Объединение текста с помощью «&»
Если вам нужно объединить текст из нескольких ячеек в одну, необязательно использовать сложные формулы. Достаточно выбрать ячейку, в которой будет соединен текст, нажать «=» и последовательно выбрать ячейки, ставя перед каждой символ «&».
Изменение регистра букв
С помощью определенных формул можно менять регистр всей текстовой информации в Excel. Функция «ПРОПИСН» делает все буквы прописными, а «СТРОЧН» - строчными. «ПРОПНАЧ» делает прописной только первую букву в каждом слове.
Внесение информации с нулями в начале
Если вы введете в Excel число 000356, то программа автоматически превратит его в 356. Если вы хотите оставить нули в начале, поставьте перед числом апостроф «’».
Ускорение ввода сложных слов
Если вы часто вводите одни и те же слова, то обрадуетесь, узнав, что в Excel есть автозамена. Она очень похожа на автозамену в смартфонах, поэтому вы сразу поймете, как ей пользоваться. С ее помощью можно заменить повторяющиеся конструкции аббревиатурами. К примеру, Екатерина Петрова - ЕП.
Больше информации
В правом нижнем углу можно следить за различной информацией. Однако мало кто знает, что, нажав туда правой кнопкой мыши, можно убрать ненужные и добавить нужные строки.
Переименование листа с помощью двойного клика
Это самый простой способ переименовать лист. Просто кликните по нему два раза левой кнопкой мыши и введите новое название.
Часто ли вы пользуетесь Excel? Если да, то у вас наверняка есть свои секреты работы с этой программой. Делитесь ими в комментариях.
