Как работать с объединенными ячейками 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).

1. Выделяем ячейки с данными. Выделяются именно ячейки, а не строки и столбцы, иначе ничего не получится.
2. Копируем их в буфер обмена сочетанием клавиш или любым другим способом.
3. Переходим к пустому листу или свободному пространству текущего листа. Важное замечание: вставлять поверх текущих данных нельзя!
4. Вставляем данные комбинацией клавиш и в меню параметров вставки выбираем вариант Транспонировать (см. рис. 11). Как вариант, вы можете использовать меню Вставить со вкладки Главная (см. рис. 12).

Рисунок 11 – Вставка с транспонированием

Рисунок 12 – Транспонирование из главного меню

Вот и всё, поворот таблицы выполнен (см. рис. 13). При этом форматирование сохраняется, а формулы изменяются в соответствии с новым положением ячеек – никакой рутинной работы не потребуется.

Рисунок 13 – Результат после поворота

Показ формул

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

Нажмите кнопку Показать формулы на вкладке Формулы (см. рис. 14), чтобы изменить представление данных на листе (см. рис. 15).

Рисунок 14 – Кнопка «Показать формулы»

Рисунок 15 – Теперь на листе видны формулы, а не результаты расчёта

Если вам трудно сориентироваться по адресам ячеек, отображаемым в строке формул, нажмите кнопку Влияющие ячейки со вкладки Формулы (см. рис. 14). Зависимости будут показаны стрелками (см. рис. 16). Для использования этой функции сначала следует выделить одну ячейку.

Рисунок 16 – Зависимости ячейки показаны стрелками

Скрываются зависимости нажатием кнопки Убрать стрелки .

Перенос строк в ячейках

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

Рисунок 17 – Надписи не помещаются в ячейки

Выделите ячейки с длинными надписями и нажмите кнопку Перенос текста на Главной вкладке (см. рис. 18), чтобы перейти к многострочному отображению (см. рис. 19).

Рисунок 18 – Кнопка «Перенос текста»

Рисунок 19 – Многострочное отображение текста

Поворот текста в ячейке

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

В зависимости от своих предпочтений вы можете пойти двумя путями:

1. Сначала создать надпись, а потом её повернуть.
2. Настроить поворот надписи в ячейке, а потом ввести текст.

Варианты различаются незначительно, поэтому рассмотрим только один из них. Для начала я объединил шесть строк в одну при помощи кнопки Объединить и поместить в центре на Главной вкладке (см. рис. 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 является основным элементом, над которым проводится большая часть операций. Работают с ячейкой так: красят, записывают какие-то значения, ее копируют, ее защищают, над ней выполняют множество операций. Часть операций, из-за частоты использования, можно смело назвать основными. К ним относятся:

Ну а теперь более подробно поговорим о том, зачем выполнять ту или иную операцию и главное как правильно работать с ячейкой в excel.

Выделить ячейку, выделить диапазон

Я уверен в что, что выделять ячейку умеет каждый. Для этого нужно просто навести мышку на нужную ячейку и нажать кнопку. Если вы ее не выделите, то не сможете проводить над ней операций. Диапазон ячеек выделяется с той же целью, но делается это несколько сложнее.

1. На мышке зажимаем левую кнопку и ведем по нужным ячейкам.

Или тоже самое с помощью клавиатуры:

1. Становимся в ячейку, начала или конца выделяемого диапазона;
2. Зажимаем Shift на клавиатуре и ведем в нужном направлении.

Выделенные ячейки можно увидеть визуально, они будут темнее. Кроме того визуально выделяются и имена строк и столбцов.

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

Без ошибки можно утверждать, что работая в excel, выделение занимает большую часть работы!

Копировать ячейку, копировать формат ячейки

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

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

Выполняя операцию копирования пользователю не нужно что-то специально нажимать, чтобы сообщить компьютеру то, что он копирует. Компьютер скопирует все, а вот во время вставки нужно думать. Копирование можно выполнить тремя способами:

• Клавиатура;
• Контекстное меню;
• Главное меню (панель операций).

Какой способ более удобный решать конечно Вам, лично я пользуюсь горячими клавишами. Нажав комбинацию Ctrl+c , вы не делаете лишних движений. Остальные операции тоже простые, в контекстном меню нажать пункт «Копировать». Либо в командной панели меню «Главное», нажать пункт «Копировать».

Помните, когда вы выполняете операцию копирования, вы копируете все, и значение, и формат!!! Разница становится при выполнении операции вставки.

Вырезать значение ячейки

Операция вырезания схожа, по сути, и по исполнению, с операцией копирования. Но имеет несколько очень важных отличительных особенности.

1. Вырезать можно значение и формат ячейки!
2. После того как вы вставляете значение в новую ячейку, значение в старой ячейке удаляется, также как и формат.

Для того чтобы было понятней, я в знакомой таблице, применил операцию вырезать для диапазона ячеек С4-С5 и вставил их в диапазон С6-С7. Как видите, вставилось не только значение, а и боковые и верхние линии ячейки.

Операцию вырезания можно выполнить тремя способами:

1. Клавиатура (нажать горячую комбинацию клавиш Ctrl+x);
2. Контекстное меню (выбрать пункт «Вырезать» — нарисованы ножнички);
3. Главное меню (в панели операций нажать кнопку с ножницами).

Вставить скопированное, вырезанное

Операция вставки является завершающим этапом копирования и вырезания. Ее можно совершить тремя способами:

1. Клавиатура (нажать горячую комбинацию клавиш Ctrl+v);
2. Контекстное меню (выбрать пункт «Параметры вставить» и воспользоваться предложенным вариантом);
3. Главное меню (в панели операций нажать кнопку вставить).

Первый способ наиболее просто, он подходит как к операции копирования, так и для вырезания. Но его недостатком при копировании является то, что с его помощью можно вставить только значение ячейки.

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

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

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

Структура Microsoft Office Excel представлена в виде ячеек, с помощью которых формируются строки и столбцы. Благодаря возможности изменять размер ячейки, объединять несколько ячеек в одну, можно создавать сложные таблицы, например, состоящие из нескольких встроенных таблиц. Количество листов документов ограничено только объемом жесткого диска вашего компьютера.

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

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

Например, пред нами стоит задача вычислить цену за всю партию товара. Для этого открываем Excel и начинаем строить таблицу. В ячейку А1 вписываем «Количество», в ячейку В1 — «Цена за единицу товара», в ячейку С1 — «Цена за всю партию». Теперь заполняем таблицу данными. Чтобы произвести расчет в ячейке С2 вводим знак =, далее кликаем мышкой по ячейке А2, нажимаем на клавиатуре знак умножения *, кликаем по ячейке В2 и нажимаем Enter.

Если все сделано правильно, мы узнаем цену за всю партию товара. Естественно, здесь показан пример самого простого расчета, так как Excel под силу даже самые сложные вычисления, поддерживает программа и встроенные языки программирования.

Чтобы документ выглядел более информативно, кроме диаграмм, сами таблицы также можно оформить, применив многочисленные возможности форматирования.

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

12 простых приёмов для эффективной работы в Excel.

Все, о чем вы хотели знать, но боялись спросить.

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

1. Быстро добавить новые данные в диаграмму:

Если для вашей уже построенной диаграммы на листе появились новые данные, которые нужно добавить, то можно просто выделить диапазон с новой информацией, скопировать его (Ctrl + C) и потом вставить прямо в диаграмму (Ctrl + V).

2. Мгновенное заполнение (Flash Fill)

Эта функция появилась только в последней версии Excel 2013, но она стоит того, чтобы обновиться до новой версии досрочно. Предположим, что у вас есть список полных ФИО (Иванов Иван Иванович), которые вам надо превратить в сокращённые (Иванов И. И.).

Чтобы выполнить такое преобразование, нужно просто начать писать желаемый текст в соседнем столбце вручную. На второй или третьей строке Excel попытается предугадать наши действия и выполнит дальнейшую обработку автоматически. Останется только нажать клавишу Enter для подтверждения, и все имена будут преобразованы мгновенно.

Подобным образом можно извлекать имена из email’ов, склеивать ФИО из фрагментов и т. д.

3. Скопировать без нарушения форматов

Вы, скорее всего, знаете про «волшебный» маркер автозаполнения - тонкий чёрный крест в правом нижнем углу ячейки, потянув за который можно скопировать содержимое ячейки или формулу сразу на несколько ячеек. Однако есть один неприятный нюанс: такое копирование часто нарушает дизайн таблицы, т. к. копируется не только формула, но и формат ячейки. Этого можно избежать, если сразу после протягивания чёрным крестом нажать на смарт-тег - специальный значок, появляющийся в правом нижнем углу скопированной области.

Если выбрать опцию «Копировать только значения» (Fill Without Formatting), то Microsoft Excel скопирует вашу формулу без формата и не будет портить оформление.

4. Отображение данных из таблицы Excel на карте

В последней версии Excel 2013 появилась возможность быстро отобразить на интерактивной карте ваши геоданные, например продажи по городам и т. п. Для этого нужно перейти в «Магазин приложений» (Office Store) на вкладке «Вставка» (Insert) и установить оттуда плагин Bing Maps.

Это можно сделать и по прямой ссылке с сайта, нажав кнопку Add. После добавления модуля его можно выбрать в выпадающем списке «Мои приложения» (My Apps) на вкладке «Вставка» (Insert) и поместить на ваш рабочий лист. Останется выделить ваши ячейки с данными и нажать на кнопку Show Locations в модуле карты, чтобы увидеть наши данные на ней.

При желании в настройках плагина можно выбрать тип диаграммы и цвета для отображения.

5. Быстрый переход к нужному листу

Также можно создать на отдельном листе оглавление с гиперссылками. Это чуть сложнее, но зачастую удобнее.

6. Преобразование строк в столбцы и обратно

Если вам когда-нибудь приходилось руками перекладывать ячейки из строк в столбцы, то вы оцените следующий трюк:

1. Выделите диапазон.
2. Скопируйте его (Ctrl + C) или, нажав на правую кнопку мыши, выберите «Копировать» (Copy).
3. Щёлкните правой кнопкой мыши по ячейке, куда хотите вставить данные, и выберите в контекстном меню один из вариантов специальной вставки - значок «Транспонировать» (Transpose).

В старых версиях Excel нет такого значка, но можно решить проблему с помощью специальной вставки (Ctrl + Alt + V) и выбора опции «Транспонировать» (Transpose).

7. Выпадающий список в ячейке

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

1. Выделите ячейку (или диапазон ячеек), в которых должно быть такое ограничение.
2. Нажмите кнопку «Проверка данных» на вкладке «Данные» (Data - Validation).
3. В выпадающем списке «Тип» (Allow) выберите вариант «Список» (List).
4. В поле «Источник» (Source) задайте диапазон, содержащий эталонные варианты элементов, которые и будут впоследствии выпадать при вводе.

Продвинутые трюки из той же серии: выпадающий список с наполнением, связанные выпадающие списки, выпадающий список с фотографией и т. д.

8. «Умная» таблица

Если выделить диапазон с данными и на вкладке «Главная» нажать «Форматировать как таблицу» (Home - Format as Table), то наш список будет преобразован в «умную» таблицу, которая (кроме модной полосатой раскраски) умеет много полезного:

1. Автоматически растягиваться при дописывании к ней новых строк или столбцов.
2. Введённые формулы автоматом будут копироваться на весь столбец.
3. Шапка такой таблицы автоматически закрепляется при прокрутке, и в ней включаются кнопки фильтра для отбора и сортировки.
4. На появившейся вкладке «Конструктор» (Design) в такую таблицу можно добавить строку итогов с автоматическим вычислением.

9. Спарклайны

Спарклайны - это нарисованные прямо в ячейках миниатюрные диаграммы, наглядно отображающие динамику наших данных. Чтобы их создать, нажмите кнопку «График» (Line) или «Гистограмма» (Columns) в группе «Спарклайны» (Sparklines) на вкладке «Вставка» (Insert). В открывшемся окне укажите диапазон с исходными числовыми данными и ячейки, куда вы хотите вывести спарклайны.

После нажатия на кнопку «ОК» Microsoft Excel создаст их в указанных ячейках. На появившейся вкладке «Конструктор» (Design) можно дополнительно настроить их цвет, тип, включить отображение минимальных и максимальных значений и т. д.

10. Восстановление несохранённых файлов

Пятница. Вечер. Долгожданный конец ударной трудовой недели. Предвкушая отдых, вы закрываете отчёт, с которым возились последнюю половину дня, и в появившемся диалоговом окне «Сохранить изменения в файле?» вдруг зачем-то жмёте «Нет».

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

На самом деле, есть неслабый шанс исправить ситуацию. Если у вас Excel 2010, то нажмите на «Файл» - «Последние» (File - Recent) и найдите в правом нижнем углу экрана кнопку «Восстановить несохранённые книги» (Recover Unsaved Workbooks). В Excel 2013 путь немного другой: «Файл» - «Сведения» - «Управление версиями» - «Восстановить несохранённые книги» (File - Properties - Recover Unsaved Workbooks). Откроется специальная папка из недр Microsoft Office, куда на такой случай сохраняются временные копии всех созданных или изменённых, но несохранённых книг.

11. Сравнение двух диапазонов на отличия и совпадения

Весьма часто при работе в Excel возникает необходимость сравнить два списка и быстро найти элементы, которые в них совпадают или отличаются. Самый быстрый и наглядный способ сделать это:

1. Выделите оба сравниваемых столбца (удерживая клавишу Ctrl).
2. Выберите на вкладке «Главная» - «Условное форматирование» - «Правила выделения ячеек» - «Повторяющиеся значения» (Home - Conditional formatting - Highlight Cell Rules - Duplicate Values).
3. Выберите вариант «Уникальные» (Unique) в раскрывающемся списке.

12. Подбор (подгонка) результатов расчёта под нужные значения

Вы когда-нибудь подбирали входные значения в вашем расчёте Excel, чтобы получить на выходе нужный результат? В такие моменты чувствуешь себя матёрым артиллеристом, правда? Всего-то пара десятков итераций «недолёт - перелёт», и вот оно, долгожданное «попадание»!

Microsoft Excel сможет сделать такую подгонку за вас, причём быстрее и точнее. Для этого нажмите на вкладке «Вставка» кнопку «Анализ „что если“» и выберите команду «Подбор параметра» (Insert - What If Analysis - Goal Seek). В появившемся окне задайте ячейку, где хотите подобрать нужное значение, желаемый результат и входную ячейку, которая должна измениться. После нажатия на «ОК» Excel выполнит до 100 «выстрелов», чтобы подобрать требуемый вами итог с точностью до 0,001.

Объединение ячеек — несложная процедура. При объединении две и более ячейки сливаются в одну более крупную. Чтобы объединить ячейки, просто выделите их и выполните команду Главная → Выравнивание → . Excel комбинирует выбранные ячейки и отображает содержимое в той, которая находится в левом верхнем углу, а содержимое выравнивается по центру объединенной ячейки.

Обычно ячейки объединяют, чтобы оптимизировать внешний вид рабочего листа. Например, на рисунке 1 изображен лист с четырьмя множествами объединенных ячеек: B2:H2, I2:O2, A4:A8, A9:A13. Текст в объединенных ячейках в столбце А записывается по вертикали.

Не забывайте, что в объединенных ячейках может содержаться только одна единица информации, например значение, текст или формула. Если вы пытаетесь объединить диапазон ячеек, в котором содержится более одной непустой ячейки, Excel выдает предупреждение, что сохранится лишь информация из одной ячейки, которая расположена ближе всего к левому верхнему углу диапазона.

Чтобы вновь разбить ячейки, выделите объединенную область и снова нажмите Объединить и поместить в центре .

Другие операции, связанные с объединением ячеек

Обратите внимание: кнопка Объединить и поместить в центре находится в раскрывающемся меню. Щелкнув на стрелке, вы увидите три дополнительных команды.

• Объединить по строкам — позволяет выбрать диапазон, а потом создать несколько объединенных ячеек (по одной на каждую выделенную строку);
• Объединить ячейки — действует почти как Объединить и поместить в центре , с той лишь разницей, что содержимое из верхней левой ячейки не выравнивается по центру, а сохраняет исходное выравнивание по горизонтали;
• Отменить объединение ячеек — разбивка выделенной объединенной ячейки.

Если в Excel требуется отображать длинный текст, удобно разбивать его на строки в объединенных ячейках. Чтобы добиться этого, выделите объединенные ячейки и выполните команду Главная → Выравнивание → Перенести текст . Пользуйтесь элементами управления для выравнивания по горизонтали и вертикали в группе Главная → Выравнивание , чтобы откорректировать положение текста.

На рис. 2 изображен лист, на котором объединена 171 ячейка (19 строк в 9 столбцах). К тексту в объединенной ячейке применен параметр Перенести текст .

Потенциальные проблемы, которые могут возникнуть с объединенными ячейками

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

Вот несколько вещей, о которых стоит помнить:

1. Нельзя использовать объединенные ячейки в таблице, создаваемой методом Вставка → Таблицы → Таблица . Это понятно, поскольку табличная информация должна быть согласована по строкам и столбцам. При объединении ячеек такая согласованность будет нарушена.
2. Как правило, можно дважды щелкнуть по заголовку столбца или строки, чтобы подогнать данные в строке или столбце, но такая возможность отсутствует, если в строке или столбце есть объединенные ячейки. Вместо этого потребуется откорректировать ширину столбца или высоту строки вручную.
3. Объединенные ячейки также могут влиять на сортировку и фильтрацию. Это еще одна причина, по которой объединение ячеек в таблицах не допускается. Если у вас есть диапазон данных, который нужно отсортировать или отфильтровать, то не пользуйтесь объединением ячеек.
4. Наконец, объединенные ячейки могут вызывать проблемы, связанные с макросами VBA. Например, при объединении ячеек в диапазоне A1:D1, команда VBA наподобие следующей выделит четыре столбца (вряд ли программист хотел этого): Columns("B:B").Select .

Как найти все объединенные ячейки на листе

Чтобы узнать, есть ли на листе объединенные ячейки, выполните следующие действия.

1. Нажмите Ctrl+F Найти и заменить .
2. Убедитесь, что в поле Найти ничего нет.
3. Нажмите кнопку Параметры , чтобы раскрыть окно.
4. Нажмите кнопку Формат , чтобы открыть диалоговое окно Найти формат , в котором указывается искомое форматирование.
5. В окне Найти формат выберите вкладку Выравнивание и установите флажок Объединение ячеек .
6. Найти формат .
7. В окне Найти и заменить нажмите кнопку Найти все .

Excel отобразит на листе все объединенные ячейки, см. рис. 3. Щелкните на адресе в списке — объединенная ячейка станет активной.

Если на этапе 3 команда Объединить и поместить в центре не выделена, это означает, что на листе нет объединенных ячеек. Если выполнить эту команду, когда выделены все ячейки, то все 17 179 869 184 ячейки рабочего листа будут объединены в одну.

Какие существуют альтернативы для объединения ячеек

В некоторых случаях можно воспользоваться командой Excel Объединить по строкам в качестве альтернативы объединению ячеек. Эта команда удобна для центирирования текста стразу в нескольких столбцах. Выполните следующие действия.

1. Введите текст, который следует центрировать в ячейке.
2. Выделите ячейку с текстом и дополнительные ячейки рядом с ней.
3. Нажмите Ctrl+1 , чтобы открыть диалоговое окно Формат ячеек .
4. В окне Формат ячеек щелкните вкладку Выравнивание .
5. В разделе Выравнивание выберите раскрывающийся список По горизонтали и укажите в нем вариант По центру выделения .
6. Нажмите OK, чтобы закрыть диалоговое окно Формат ячеек .

Текст будет выровнен по центру в выделенном диапазоне.

Еще одна альтернатива объединению ячеек заключается в использованию надписей. Это особенно удобно при работе с текстом, который должен отображаться вертикально. На рисунке 4 приведен пример надписи, в которой выведен вертикальный текст.

Чтобы добавить надпись, выполните команду Вставка → Текст → Надпись , отрисуйте надпись на листе, а затем введите текст. Пользуйтесь инструментами форматирования текста вкладки Главная, чтобы корректировать текст, а также инструментами контекстной вкладки Средства рисования → Формат , чтобы вносить изменения, например можно скрыть контур надписи.

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

В этом уроке вы узнаете, как выделять ячейки, вводить содержимое, удалять ячейки и их содержимое. Вы также узнаете, как вырезать, копировать и вставлять ячейки, перетаскивать и заполнять ячейки с помощью маркера заполнения.

Ячейки и их содержимое

Ячейки – основные строительные блоки листа. Ячейки могут иметь разнообразное содержимое, например, текст , формулы или функции . Чтобы работать с ячейками, вам нужно знать как их выделять, вводить содержимое, удалять ячейки и их содержимое.

Ячейка

Каждый прямоугольник на листе называется ячейкой. Ячейка – это пресечение строки и столбца.

У каждой ячейки есть имя или адрес ячейки , который образуется на основе имен столбца и строки, пресечением которых образована ячейка. Адрес выбранной ячейки показывается в поле Имя. Здесь вы можете увидеть, что выбрана ячейка C5 .

Также вы можете одновременно выделить несколько ячеек. Группа ячеек называется диапазоном ячеек . Если вы ссылаетесь не на одну ячейку, а на диапазон, то используйте запись из адресов первой и последней ячеек, разделенных двоеточием. Например, диапазон ячеек, включающий ячейки A1, A2, A3, A4 и A5 будет записан как A1:A5 .

Чтобы выбрать ячейку:

Вы также можете перемещаться между ячейками и выделять их с помощью клавиш со стрелками на клавиатуре.

Чтобы выбрать несколько ячеек:

Каждая ячейка может содержать свой собственный текст, форматирование, комментарии, формулы или функции.

Текст
Ячейки могут содержать буквы, цифры и даты.

Атрибуты форматирования
Ячейки могут содержать атрибуты форматирования, которые меняют способ отображения букв, цифр и дат. Например, даты могут быть отформатированы как ММ/ДД/ГГГГ или Месяц/Д/ГГГГ.

Комментарии
Ячейки могут содержать комментарии от нескольких рецензентов.

Формулы и функции
Ячейки могут содержать формулы и функции, которые вычисляют значения ячейки. Например, СУММ (ячейка 1, ячейка 2…) – это формула, которая суммирует значения нескольких ячеек.

Чтобы ввести содержимое:

1. Кликните по ячейке, чтобы выделить ее.
2. Введите содержимое в выбранную ячейку с помощью клавиатуры. Оно появится в ячейке и строке формул. Вы также можете вносить и редактировать содержимое в строке формул.

Чтобы удалить содержимое ячейки:

1. Выделите нужную ячейку.
2. Кликните по команде Очистить на ленте. Появится диалоговое окно.
3. Выберите Очистить содержимое.

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

Чтобы удалить ячейки:

1. Выделите ячейки, которые хотите удалить.
2. На ленте выберите команду Удалить.

Существует огромная разница между удалением содержимого ячейки и удалением самой ячейки . Если вы удалите ячейку, то по умолчанию ячейки, находящиеся ниже удаляемой, поднимутся вверх на ее место.

Чтобы скопировать и вставить содержимое ячеек:

Чтобы вырезать и вставить содержимое ячеек:

Для доступа к параметрам вставки:

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

Чтобы получить доступ к командам форматирования:

1. Выделите ячейки, которые нужно отформатировать.
2. Кликните правой кнопкой мыши по выделенным ячейкам. Появится диалоговое окно, где вы сможете легко найти множество команд, которые также есть на ленте.

Чтобы переместить ячейки:

Чтобы использовать маркер заполнения для заполнения ячеек:

Практикуйте!

1. Откройте существующую книгу Excel 2010.
2. Выберите ячейку D3 , и обратите внимание, как выглядит его адрес в поле Имя и его содержимое в строке формул.
3. Попробуйте написать текст и цифры.
4. Используйте маркер заполнения, чтобы заполнить прилегающие ячейки по вертикали и по горизонтали.
5. Вырежьте ячейки и вставьте их в другое место.
6. Удалите ячейку и обратите внимание, как ячейки под ним сместятся вверх.
7. Попробуйте перетащить ячейки.