Отчёт по EXCEL

При переходе в режим ввода формул поле Имя (Name), расположенное в левой части строки формул, заменяется кнопкой, на которой отображена последняя использовавшаяся функция. Расположенная справа от нее кнопка со стрелкой открывает список, который содержит 10 последних использовавшихся функций и пункт Другие функции (More/unctions) (рис. 16.1).

 

 

       

 

                  Рис. 16.1. Окно Excel в режиме ввода формул

 

 

 

 

 

 

 

 

 

 

В качестве примера введем формулу  в ячейку D4. Закончив ввод формулы, нажмем клавишу Enter или щелкнем в строке формул по кнопке Ввод (Enter). В ячейке, содержащей формулу, отобразится результат вычисления, сама формула будет видна в строке формул (рис. 16.2

 

 

 

 

         Рис. 16.2. Отображение расчетной формулы в строке формул

 

 

 

 

      

               

 

 

 

 

 

 

         Использование ссылок в формуле

 

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

 

 

Таблица 16.3. Операторы ссылки

 

 

Операторы ссылки	Значение (пример)
: (двоеточие)	Ставится между ссылками на первую и последнюю ячейки диапазона. Такое сочетание является ссылкой на диапазон (В5:В15).
; (точка с запятой)	Оператор объединения. Объединяет несколько  ссылок в одну ссылку (СУММ(В5:В15;О5:В15)).
(пробел)	Оператор пересечения множеств, служит для ссылки на общие ячейки двух диапазонов (B7:D7 С6:С8).

 

Чтобы сослаться на диапазон ячеек, введите ссылку на верхнюю левую  ячейку диапазона, поставьте двоеточие (:), а затем – ссылку на правый нижний угол диапазона. Например, А2:С5. Ссылка на все ячейки между 6-й и 15-й строками включительно имеет вид 6:15, на все ячейки в столбце С – С:С.

Для задания обращения ко всем ячейкам, находящимся в нескольких диапазонах одновременно используется пересечение. Для задания пересечения диапазонов используется пробел. Например, формула =СУММ(А2:В3 В2:С3) вычисляет сумму чисел в пересекающихся диапазонах А2:ВЗ и В2:СЗ. Для обращения ко всем ячейкам, включенным в два диапазона, используется объединение. Если в ячейке ВЗ записано Петров, а ячейке A3 – Владимир, то формула АЗ=В3&", "&АЗ будет означать объединение указанных выше имен, разделенных запятой (Петров, Владимир). При использовании операторов объединения и пересечения удобно пользоваться именованными диапазонами.

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

Примеры записей диапазонов ячеек  в функции:

=СУММ(Е:Е) – определяется сумма  числовых значений, содержащихся  в столбце Е;

=СУММ(Е2:С5) – подсчитывается сумма числовых значений, расположенных в диапазоне от Е2 до G5;

=СУММ(5:5) – суммируются данные  всех ячеек пятой строки;

=СУММ(2;4) – подсчитывается сумма  2+4.

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

=СУММ(С8;О4;Е2:Е5;Р5)

    

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

 

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

 

           Отображение формулы в ячейке

 

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

Для возврата к принятому режиму отображения формул снимите флажок.. Чтобы перейти в режим отображения формул или вернуться в обычный режим нажмите клавиши CTRL+' (клавиша обратного апострофа находится на той же клавише, что и тильда "~" – ниже клавиши Esc).

 

              Подсказки аргументов функций

 

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

 

        Абсолютная и относительная ссылка

 

Ссылка – это указание адреса ячейки. Различают относительные  или абсолютные ссылки.

 

Например, нам надо подсчитать сумму  чисел, хранящихся в ячейках A1, A2, A3. Щелкнем дважды ячейку А5, и поместим в нее формулу =А1+А2+А3. Чтобы вычислить сумму чисел, хранящихся в ячейках C1, C2, С3, можно не писать заново формулу, а скопировать ее из ячейки А5 в G5 (см. главу 14, раздел "Копирование и перемещение содержимого ячеек"). Excel автоматически изменит относительные ссылки и формула примет вид =С1+С2+С3. Измененную формулу можно увидеть, щелкнув ячейку С5. (Для отображения формул в окне приложения установите флажок формулы на вкладке Вид диалогового окна Параметры (рис. 19.8).)

Например, если скопировать формулу  из ячейки А7 с абсолютными ссылками в ячейку С7, то формула не изменится. Для указания абсолютной ссылки используется знак доллара $. $А$7, $С$7 (рис. 16.3). Адреса ячеек, использованные в формуле, и границы этих ячеек будут закрашены в одинаковые цвета.

 

 

 

 

   Рис. 16.3. Отображение формул с абсолютными, и относительными ссылками в окне Excel

 

        

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  Построение диаграммы с помощью мастера

 

Построение диаграммы облегчает  Мастер диаграмм. Он разбивает процесс  создания диаграммы на несколько  шагов.

Для отображения диалогового окна Мастер диаграмм (Chart Wizard) можно воспользоваться контекстным меню, щелкнув правой кнопкой мыши ярлычок рабочего листа и выбрав в меню команду Добавить (Insert). Появится диалоговое окно Вставка (Insert), в котором на вкладке Общие (General) выделите значок Диаграмма (Chart) и нажмите кнопку ОК.

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

 

                  Выбор типа и вица диаграммы

 

Диалоговое окно Мастер диаграмм (шаг 1 из 4): тип диаграммы (Chart Wizard – Step 1 of 4 – Chart Type) имеет две вкладки (рис. 17.1), на одной из которых вы должны выбрать сначала тип диаграммы в списке 7мл (Chart type), а затем разновидность в окне Вид (Chart sub-type).

 

 

 

Рис. 17.1. Диалоговое окно, позволяющее  выбрать тип диаграммы

 

 

Каждый тип  диаграммы имеет несколько разновидностей. Например, стандартная гистограмма  представлена в 7 вариантах, линейчатая диаграмма – в 6 вариантах (рис. 17.2). Можно оставить тип и вид, выделенный по умолчанию.

Чтобы увидеть, как ваши данные будут выглядеть  при выборе различных типов диаграмм, нажмите и не отпускайте кнопку Просмотр результата (Press and hold to View Sample). Поле Вид (Chart sub-type) будет заменено полем Образец (Sample), в котором будет отображена диаграмма. Кнопка в левом нижнем углу диалогового окна позволяет запустить помощника, вывести на экран советы и справки, помогающие при создании диаграммы.

 

 

 

 

 

 

                               

 

                       Рис. 17.2. Шесть вариантов линейчатой диаграммы

 

 

 

 

 

 

 

 

 

 

Тип диаграммы	Область применения
Гистограмма (Column charts)	Показывает изменение данных на протяжении отрезка времени. Для наглядного сравнения различных величин  используются вертикальные столбцы, которые  могут быть объемными и плоскими. Высота столбца пропорциональна  значению, представленному в таблице. Трехмерная гистограмма показывает раскладку значений по категориям и  рядам данных.Ось категорий в гистограмме располагается по горизонтали, ось, значений – по вертикали. Такое расположение осей подчеркивает характер изменения значений во времени. На объемной гистограмме. с перспективой сравниваемые значения располагаются в плоскости (вдоль двух осей). Гистограмма с накоплением позволяет представить отношение отдельных составляющих к их совокупному значению. Гистограмма может быть нормированной на 100%*.
Линейчатая (Bar chart)	Дает возможность сравнивать значения различных показателей. Внешне напоминают повернутые на 90 градусов гистограммы. Ось категорий расположена по вертикали, ось значений – по горизонтали. Это позволяет обратить большее  внимание на сравниваемые значения, чем  на время. Может быть построена с накоплением, чтобы показать вклад отдельных элементов в общую сумму, и нормированной на 100%**.
График (Line chart)	Показывает, как меняется один из показателей (Y) при изменении другого показателя (X) с заданным шагом. Excel позволяет построить объемные графики и ленточные диаграммы. График с накоплением отображает изменение общей суммы по времени или по категории.
Точечная диаграмма (Scatter chart)	Показывает изменение численных  значений нескольких рядов данных (ось Y) через неравные промежутки (ось X), или отображает две группы чисел  как один ряд координат х и  у. Располагая данные, поместите значения х в один столбец или одну строку, а соответствующие значения у в соседние строки или столбцы. Обычно используется для научных данных.
Лепестковая диаграмма (Radar chart)	Вводит для каждой категории собственные  оси координат, расходящиеся лучами из начала координат. Линии соединяют  значения, относящиеся к одному ряду. Позволяет сравнивать совокупные значения нескольких рядов данных. Например, при сопоставлении количества витаминов  в разных соках образец, охватывающий наибольшую площадь, содержит максимальное количество витаминов.
Кольцевая диаграмма (Doughnut chart)	Позволяет показать отношение частей к целому. Может включать несколько  рядов данных. Каждое кольцо кольцевой  диаграмме соответствует одному.ряду данных.
Диаграмма с областями (Area chart)	Показывает изменения, происходящие с течением времени. Отличается от графиков тем, что позволяет показать изменение  суммы значений всех рядов данных и вклад каждого ряда.

 

Корректирование размеров источника данных диаграммы

 

На втором шаге построения диаграммы  Мастер диаграмм позволяет откорректировать размеры выделенного диапазона  с данными. На вкладке Диапазон данных (Data Range) кнопка свертывания диалогового окна справа от поля Диапазон (Data range) позволяет временно свернуть окно и выбрать мышью новый диапазон ячеек (рис. 17.4). На листе с данными вокруг выделенного диапазона ячеек будет видна бегущая рамка. Закончив выделение диапазона, нажмите эту кнопку снова для восстановления диалогового окна.

 

 

 

Рис. 17.4. Диалоговое окно, используемое для задания источника данных

 

На вкладке демонстрируется  вид диаграммы с учетом заданных параметров, Положением переключателя: Ряды в строках (Rows) или в Столбцах (Columns) можно изменить расположение данных на листе.

На вкладке Ряд (Series) в диалоговом окне Мастер диаграмм (шаг 2 из 4): источник данных диаграммы (Chart Wizard – Step 2 of 4 – Chart Source Data) отображен список названий существующих рядов данных. Ряды данных можно добавить в диаграмму или удалить из нее без изменения данных на листе. Большинство типов диаграмм может быть представлено несколькими рядами данных. Исключение составляет круговая диаграмма, отображающая только один ряд данных. Для точечной и пузырьковой диаграмм изменить можно только тип всей диаграммы.

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

Названия рядов можно изменить на вкладке (Series) в поле Имя (Name), не изменяя при этом текст на листе. Введенное название будет отображено в легенде для выбранного ряда. Это название не связано с ячейкой листа. Чтобы добавить значения нового ряда, выберите или введите новый диапазон в поле Значения (Values). При вводе данных в это поле на лист они не добавляются.

 

 

Оформление диаграммы

На третьем шаге построения диаграммы  в диалоговом окне Мастер диаграмм (шаг 3 из 4) параметры диаграммы (Chart Wizard – Step 3 of 4 – Chart Options) пользователю предлагается заполнить поля шести вкладок, определяющих оформление диаграммы. Например, открыв вкладку Заголовки (Titles) (рис. 17.5), можно ввести в соответствующие поля Название диаграммы (Chart Title), надписи по осям координат: Ось X(категорий) (Category (X) axis) и Ось Y (значений) (Value (Z) axis). Введенный текст отображается в обрамлении маркеров. Маркеры позволяют перетащить текст в другое место диаграммы. Для замены текста на диаграмме выделите его и введите новый.

 

 

 
Рис. 17.5. Диалоговое окно Мастера диаграмм, с открытой вкладкой Заголовки

 

 

 

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

.

 

 

 

Пример

Данные для диаграмм 

Фамилия Имя Отчество	Начислено	Удержано
Авилов А.А	10849	6805
Аникина А.Б	12198	7571
Барабанова В.П	11276	6782
Ишмуратов И.Ф	25267	16255
Лыков Д.А	17356	10976
Кангина Д.Р	17725	11089
Крутиев М.В	19192	12307
Каличко Л.И	20438	12957
Карелина А.Л	13391,19	8213
Каробова И.О	15617,27	9949
Розов К.А	14243,26	8890
Пугач Н.А	12939,80	7942
Узакова Т.А	9955,55	6265
Фальковская Н.А	18699,98	11786
Шестухина В.Ю	16899,98	10460
Якубовская К.Р	15833,32	10083
Кисляк В.Р	16091,34	10079
Макарова В,С	19182,57	12017
Макарова Ю.С	18333,32	11628
Якубовская К.Р	16390,42	10181

 

 

 

Пример

 

 

 

 

 

 

 

                               Печать документа

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