Текст книги "Excel. Легкий старт"

Написание формул и расчет итогов

Как отмечалось выше, от выручки исчисляется налог в размере 5 %. Для автоматического расчета налога введем в ячейку Е7 формулу = D7/100*5. После нажатия клавиши Enter в ячейке Е7 отобразится рассчитанная сумма налога. Затем установим курсор в ячейку Е7, скопируем ее содержимое (формулу) в буфер обмена, выделим диапазон Е8:Е15 и нажмем клавишу Enter – в результате будет рассчитана сумма налога во всех выделенных ячейках. При этом формула будет скопирована корректно, с учетом координат ячеек (например, в ячейке Е8 она будет выглядеть так: =D8/100*5). Затем аналогичным образом вставим формулы в диапазоны G7:G15 и 17:115. Результат выполненных действий показан на рис. 6.6.

Рис. 6.6. Расчет суммы налога по всем товарным позициям

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

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

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

• Из раскрывающегося списка При каждом изменении в выбираем значение Группа товаров.

• Из списка Операция выбираем значение Сумма.

• В области Добавить итоги по устанавливаем следующие флажки: Январь, Столбец Е, Февраль, Столбец G, Март, Столбец I.

• В нижней части окна устанавливаем флажок Итоги под данными.

После выполнения указанных действий и нажатия в данном окне кнопки ОК таблица примет вид, представленный на рис. 6.7.

Рис. 6.7. Расчет промежуточных и общих итогов

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

Создание фигурного заголовка таблицы

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

Рис. 6.8. Выбор фигурной надписи

В первую очередь необходимо включить отображение панели инструментов WordArt – для этого следует выполнить команду главного меню Вид → Панели инструментов → WordArt. Затем на этой панели следует нажать кнопку Добавить объект WordArt и в открывшемся окне выбрать подходящий образец (рис. 6.8).

На рисунке в данном окне выбрана третья слева надпись в первом ряду. После нажатия кнопки ОК открывается окно настройки надписи. Оставляем без изменения параметры, предложенные по умолчанию, лишь вводим в поле Текст заголовок нашей таблицы – Выручка от реализации. Нажимаем кнопку ОК – созданная надпись отобразится на экране. Нам остается лишь перетащить ее с помощью мыши на специально оставленное место над таблицей (рис. 6.9).

Рис. 6.9. Фигурный заголовок таблицы

При необходимости можно изменить параметры фигурного текста. Для этого следует воспользоваться соответствующими командами контекстного меню (подробнее об этом рассказано в разд. 3).

Построение диаграммы на основе табличных данных

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

В нашей таблице выделяем три ячейки: D19, F19 и Н19 (они содержат итоговые суммы выручки в каждом месяце квартала). Затем выполняем команду Вставка → Диаграмма – в результате откроется окно мастера диаграмм (см. рис. 3.5). В этом окне устанавливаем указанные ниже параметры.

• На первом этапе создания диаграммы выбираем стандартную круговую диаграмму.

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

• На третьем этапе на вкладке Заголовки в соответствующем поле указываем название диаграммы – Динамика выручки от реализации. Параметры, предложенные по умолчанию на вкладке Легенда, оставляем без изменений. На вкладке Подписи данных устанавливаем флажки значения и доли.

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

После нажатия кнопки Готово откроется лист с диаграммой, которая изображена на рис. 6.10.

Рис. 6.10. Круговая диаграмма, построенная на основе табличных данных

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

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

1. Первым делом выделим три диапазона ячеек: C7:D9, C11:D13 hC15:D17 (рис. 6.11).

Рис. 6.11. Выделение диапазонов ячеек

2. Выполним команду главного меню Вставка → Диаграмма.

3. В открывшемся окне мастера диаграмм на вкладке Стандартные выберем обычную гистограмму.

4. На втором этапе построения диаграммы оставим предложенные по умолчанию значения параметров.

5. На третьем шаге на вкладке Заголовки в поле Название диаграммы введем значение Выручка по видам товаров за январь, в поле Ось X (категорий) – Виды товаров, а в поле Ось Y (значений) – Размер выручки. На вкладке Подписи данных установим флажок значения. На всех остальных вкладках данного окна оставляем настройки, установленные по умолчанию.

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

После нажатия кнопки Готово на автоматически созданном рабочем листе будет построена гистограмма, которая представлена на рис. 6.12.

Рис. 6.12. Гистограмма, построенная на основе табличных данных

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

Добавление необходимых примечаний

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

Рис. 6.13. Отображение всех примечаний

Выделим ячейку D7, выполним команду Вставка → Примечание (или выберем в контекстном меню пункт Добавить примечание) и в открывшемся окошке под именем пользователя с клавиатуры введем текст примечания: С учетом старых запасов (для завершения ввода примечания достаточно щелкнуть в любом месте рабочей области). Аналогичным образом к ячейке F14 добавим примечание Выручка рассчитана по отгрузке.

По умолчанию в правом верхнем углу каждой ячейки с примечаниями появится маленький красный треугольник, а текст примечания можно будет увидеть только при наведении на ячейку указателя мыши. Однако в процессе работы могут возникать ситуации, когда необходимо увидеть все созданные ранее примечания ко всем ячейкам. Для этого нужно выполнить команду Сервис → Параметры и в открывшемся окне на вкладке Вид (см. рис. 2.13) установить переключатель Примечания в положение примечание и индикатор, после чего нажать кнопку ОК. В результате выполненных действий отобразятся все созданные примечания (рис. 6.13).

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

7. Тонкости и нештатные ситуации

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

Как известно, для вывода на печать какой-нибудь области рабочего листа нужно выделить ее, а затем в диалоговом окне Печать установить режим выделенный диапазон. При этом, если необходимо распечатать несколько разных выделенных областей, приходится повторять данную операцию каждый раз. Можно ли автоматизировать этот процесс?

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

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

Возможно, в данном случае будет целесообразным изменение масштаба документа. Для этого нужно выполнить команду Файл → Параметры страницы и в открывшемся окне на вкладке Страница установить переключатель в положение разместить не более чем на 1 стр. в ширину и 1 стр. в высоту. При повторном вызове окна Параметры страницы можно определить, в каком масштабе будет распечатан документ – это значение отобразится в поле установить X % от натуральной величины.

Можно ли сделать так, чтобы документ распечатывался вместе с сеткой?

По умолчанию Excel не выводит на печать сетку рабочего листа. Однако при необходимости эту настройку можно изменить. Чтобы документ выводился на печать вместе с сеткой, необходимо в окне Параметры страницы (открывается с помощью команды Файл → Параметры страницы) на вкладке Лист установить флажок сетка.

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

Можно ли каким-либо образом изменять цвет ярлыков рабочих листов, и если да, то как это сделать?

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

Можно ли отключить отображение в рабочей области заголовков строк и столбцов?

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

Каким образом можно установить произвольный размер листа, например Х столбцов на Y строк?

Наиболее приемлемый вариант – просто скрыть все лишние строки и столбцы с помощью команд Формат → Строка → Скрыть и Формат → Столбец → Скрыть соответственно.

Можно ли увеличить количество строк и столбцов, находящихся на рабочем листе?

В настоящее время невозможно увеличить количество строк и столбцов рабочего листа, поскольку оно жестко зафиксировано разработчиком – корпорацией Microsoft.

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

Причина заключается в настройках автозамены. Решение данной проблемы такое: нужно выполнить команду Сервис → Параметры автозамены и в открывшемся окне на вкладке Автозамена снять флажок Исправлять ДВе ПРописные буквы в начале слова. Здесь же можно отредактировать и другие параметры автозамены.

Что представляет собой организатор картинок Microsoft, который можно использовать в Excel?

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

Для перехода в режим работы с организатором картинок предназначена команда Вставка → Рисунок → Картинки.

Можно ли каким-нибудь способом оптимизировать запуск Excel?

Для оптимизации запуска программы существует несколько способов. Например, чтобы отключить отображение заставки, которая требует определенных ресурсов компьютера при загрузке Excel, необходимо выполнить следующее: щелкнуть правой кнопкой мыши на ярлыке Excel на Рабочем столе (либо на соответствующем пункте меню Пуск) и в появившемся контекстном меню выбрать пункт Свойства. В открывшемся окне нужно перейти на вкладку Ярлык и в поле Объект в конце строки добавить пробел и символы /Е (не вместо имеющегося значения, а именно в конце строки!). После нажатия в данном окне кнопки ОК программа Excel будет запускаться без экранной заставки, что несколько ускорит процесс загрузки.

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

Как сделать так, чтобы при открытии меню все команды отображались сразу, а не с задержкой?

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

Существует ли возможность изменения предложенного по умолчанию размера шрифта ярлыков рабочих листов?

Такая возможность существует, но соответствующие настройки задаются не в Excel, а в разделе Экран окна Панель управления. Для перехода в режим настройки размера шрифта, используемого для ярлыков рабочих листов, нужно выполнить команду Пуск → Панель управления → Экран, в открывшемся окне перейти на вкладку Оформление и нажать кнопку Дополнительно – в результате появится окно Дополнительное оформление. Из раскрывающегося списка Элемент следует выбрать значение Полоса прокрутки и установить требуемую величину шрифта в поле Размер. При этом необходимо учитывать, что внесенные изменения коснутся и других элементов интерфейса (в частности, соответствующим образом изменятся полосы прокрутки в окнах приложений).

Можно ли изменить цвет и шрифт комментариев к ячейкам, которые используются в программе по умолчанию?

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

Можно ли изменить шрифт, используемый по умолчанию для новой книги?

Для этого следует выполнить команду Сервис → Параметры и в открывшемся окне перейти на вкладку Общие. Здесь в поле Стандартный шрифт из раскрывающегося списка можно выбрать шрифт, а в поле Размер – указать размер выбранного шрифта. Оба этих параметра будут использованы по умолчанию при создании новой книги, листа либо при следующем запуске Excel.

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

Для решения данной задачи нужно выполнить команду Сервис → Параметры и в открывшемся окне на вкладке Общие в поле Листов в новой книге установить требуемое количество рабочих листов. При этом следует учитывать, что максимально возможное значение данного поля – 255.

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

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

Можно ли изменить установленный по умолчанию тип диаграммы?

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

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

Как перетащить ячейку на другой рабочий лист?

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

Можно ли при установленном формате с двумя знаками после запятой сделать так, чтобы нулевые значения не отображались?

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

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

Можно ли в Excel вводить числа как текст?

Да, такая возможность существует. В программе Microsoft Excel числа хранятся в виде числовых данных, даже если к содержащим их ячейкам был применен текстовый формат. Если такие данные, например коды товаров, необходимо интерпретировать как текст, то сначала желательно применить текстовый формат к пустым ячейкам (выполните команду Формат → Ячейки и в открывшемся окне на вкладке Число выберите формат Текстовый), а затем ввести числа.

Если числа уже введены, назначьте ячейкам текстовый формат и, выделяя каждую ячейку, нажимайте клавишу F2, а затем – Enter, чтобы ввести данные заново.

Сколько значащих цифр поддерживается в программе Excel?

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

Как известно, при использовании в расчетах формул в ячейках отображается не сама формула, а результат ее вычисления. Однако при необходимости проверить правильность формул (особенно при работе с большими объемами информации) это неудобно, поскольку для проверки каждой формулы нужно устанавливать курсор в соответствующую ячейку. Можно ли сделать так, чтобы вместо результатов вычислений в ячейках отображались сами формулы? И если да, то как потом вернуться к первоначальному состоянию?

Данная проблема решается штатными средствами программы Excel. Для этого нужно воспользоваться командой Сервис → Зависимости формул → Режим проверки формул, при выполнении которой происходит переход в режим отображения формул. Для возврата к первоначальному состоянию следует повторно выполнить данную команду.

Можно ли выполнить одновременное форматирование нескольких рабочих листов?

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

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

Такая возможность есть. Для подобных целей удобно использовать условное форматирование. Переход в данный режим работы выполняется с помощью команды Формат → Условное форматирование.

Данные на рабочем листе книги Excel расположены в нескольких диапазонах, которые разделены между собой пустыми строками или столбцами. Можно ли быстро выделить один из этих диапазонов?

Оптимальный в данном случае способ – это установить курсор в любую ячейку внутри диапазона и нажать сочетание клавиш Ctrl+* (звездочка – на цифровой клавиатуре). В результате будет полностью выделен диапазон с активной ячейкой.

Как программа Excel определяет, какой числовой формат необходимо использовать?

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

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

Как отобразить числовое представление даты или времени?

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

Каким образом в программе Excel обрабатываются даты и время – как текст или как числа?

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

Какие символы при работе в Excel могут быть интерпретированы как числа?

В программе Microsoft Excel число может содержать только следующие знаки:

0123456789 + – (),/$%.Ее

Все знаки «плюс», стоящие перед числами, игнорируются, а запятая интерпретируется как разделитель десятичных разрядов. Все другие сочетания знаков, состоящие из цифр и иных символов, распознаются в программе как текст.

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

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

Если рабочая книга включает в себя диалоговые листы Excel 5/95, то нужно выделить каждый из объектов диалогового окна, чтобы просмотреть связанную с ним формулу. Если объект ссылается на другую рабочую книгу, то эту ссылку можно удалить или отредактировать.

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

Нужно удалить те имена, которые ссыпаются на другую рабочую книгу или имеют неправильные ссылки (#ССЫЛКА!).