Оглавление

Введение

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

      До  появления компьютеров таблицы  создавались на бумаге в виде разграфленных листов или картотеки. Компьютеры облегчили не только отображение данных, но и их обработку. Программы, используемые для этой цели, получили название табличных процессоров или электронных таблиц. Электронная таблица, как и разграфленная на бумаге, разделена на столбцы и строки, в ячейки которых записана различного рода информация: тексты, числа, формулы и т.д.  В настоящее время на рынке известно большое количество программ, обеспечивающих хранение и обработку табличных данных: Microsoft Excel, Lotus l-2-З, Quattro Pro и другие.

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

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

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

      Электронные таблицы можно легко вставить в документ, составленный в текстовом  процессоре Word или другом приложении Microsoft Office.

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

     Объект  исследования: табличный процессор  MS Excel.

     Предмет исследования: мастера в MS Excel.

     Цель работы рассмотреть какие мастера и для чего используются в MS Excel.

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

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

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

1.Рабочая  книга. Лист. Ячейка

       Каждый  файл Excel называется рабочей книгой. Рабочая книга – это документ (файл), который мы открываем, сохраняем, копируем, удаляем. Каждая рабочая книга содержит три листа рабочих таблиц, также возможно создавать дополнительные листы. Для того чтобы ориентироваться в них, в Excel предусмотрены ярлыки с именами рабочих листов от Лист 1 до Лист 3. Каждый лист в рабочей книге, в свою очередь, разбит приблизительно на 16 миллионов ячеек, в каждую из которых можно вводить данные.

Таблица 1 Рабочая книга. Лист 

      По краям рабочей таблицы Excel находится рамка с обозначениями строк и столбцов: столбцам (всего их 256) соответствуют буквы, а строкам – числа (от 1 до 65536). И столбцы и строки имеют большое значение, поскольку именно они составляют адрес ячейки, например А 1. Но, кроме системы А 1, Excel  поддерживает еще более старую, но в тоже время более корректную систему адресации ячеек R1C1. В ней пронумерованы и строки (rows) и столбцы (columns) рабочей таблицы, причем номер строки предшествует номеру столбца.

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

2. Понятие  функции. Мастер  функций.

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

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

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

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

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

Как числовое значение (например, 89 или – 5,76),

Как координату ячейки (это наиболее распространенный вариант),

Как диапазон ячеек (например, С3:F3).

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

      Безусловно, функцию можно ввести, набрав ее прямо в ячейке. Однако Excel предоставляет  на стандартной панели инструментов кнопку Вставка функции. В открывшемся диалоговом окне Мастер функций – шаг 1 указывается нужная функция, затем Excel выводит диалоговое окно Аргументы функции, в котором необходимо ввести аргументы функции.

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

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

1. 10 недавно использовавшихся,
2. полный алфавитный перечень,
3. финансовые,
4. дата и время,
5. математические,
6. статистические,
7. ссылки и массивы,
8. работа с базой данных,
9. текстовые,
10. логические,
11. проверка свойств и значений.

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

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

      Для создания формулы следует выделить ячейку и нажать кнопку Вставка функции в строке формул. Можно также нажать комбинацию клавиш клавиатуры Shift + F3. Например, для создания в ячейке А11 формулы для округления значения в ячейке А10 таблицы на рис. 1, следует выделить ячейку А11.

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

Таблица 2 Мастер функций

      Например, для округления числа следует  выбрать категорию Математические, а функцию ОКРУГЛ.

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

Таблица 3 Мастер функций. Выбор функции 

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

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

Таблица 4. Функция округления 

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

       Текст, числа и логические выражения  в качестве аргументов обычно вводят с клавиатуры.

      Аргументы в поля можно вводить в любом  порядке.

Например, в таблице округляемое значение находится в ячейке А10, следовательно, в поле Число диалогового окна аргументы функции указана ссылка на эту ячейку. А в поле Число разрядов аргумент 2 введен с клавиатуры.

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

       Следует иметь в виду, что некоторые функции не имеют аргументов.

По окончании  создания функции следует нажать кнопку ОК или клавишу клавиатуры Enter.

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

      После щелчка на кнопке Изменить формулу Excel открывает диалоговое окно Аргументы функции, в котором можно отредактировать аргументы, указав новые ссылки на ячейки или диапазоны ячеек в текстовых полях Число 1, Число 2 и т.д. если необходимо заменить текущий аргумент, то его необходимо выделить, а затем удалить, нажав клавишу <Del>, и лишь после этого вводить новое значение аргумента.

      Завершая  рассказ о функциях, мы рассмотрим некоторые из них более подробно. Итак, один из инструментов это Автосумма, он находится на стандартной панели инструментов (обозначен греческой буквой    ).

Таблица 5.  Функция Автосумма 

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

Таблица 6. Функция СУММ 

      Итак, по умолчанию Автосумма помещает в текущую ячейку функцию СУММ, однако это можно изменить. Чтобы с помощью этого инструмента можно было поместить другую функцию (СРЗНАЧ, МАКС или МИН), необходимо щелкнуть на кнопке со стрелкой вниз справа от него и выбрать в раскрывающемся меню имя нужной функции. При щелчке на кнопке Дополнительно Excel откроет диалоговое окно Вставка функции.

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

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

      Иногда  требуется вычислить среднее  значение. Здесь можно выделить несколько случаев:

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

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

      Нажмите на панели инструментов Стандартные стрелку рядом с кнопкой Автосумма, а затем выберите команду Среднее и нажмите клавишу ВВОД.

      Таблица 7.Функция среднего значения ячейки 

       Вычисление среднего значения ячеек, расположенных вразброс. Для выполнения этой задачи используется функция СРЗНАЧ, которая возвращает среднее (арифметическое) своих аргументов. Причем аргументов может быть от 1 до 30, и они  должны быть либо числами, либо именами, массивами или ссылками, содержащими числа.

      Вычисление  среднего взвешенного значения. Для  этого используются функции СУММПРОИЗВ и СУММ. Итак, функция СУММПРОИЗВ перемножает соответствующие элементы заданных массивов и возвращает сумму произведений. Массивов, чьи компоненты нужно перемножить, а затем сложить может быть от 2 до 30 массивов.

      Однако  следует помнить, что аргументы, которые являются массивами, должны иметь одинаковые размерности. Если это не так, то функция СУММПРОИЗВ возвращает значение ошибки #ЗНАЧ! А также то, что СУММПРОИЗВ трактует нечисловые элементы массивов как нулевые.

      Функция СУММ, как уже упоминалось выше, суммирует все числа в интервале ячеек. Причем, учитываются числа, логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов.

Если  аргумент является массивом или ссылкой, то только числа учитываются в массиве или ссылке. Пустые ячейки, логические значения, тексты и значения ошибок в массиве или ссылке игнорируются.

      Аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, вызывают значения ошибок.

      Вычисление  среднего значения всех чисел, кроме  нулевых (0). Для выполнения этой задачи используются функции СРЗНАЧ и ЕСЛИ.

3. Мастер диаграмм в табличном процессоре Excel

3.1. Общие сведение о диаграммах.

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

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

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

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

      Диаграмма постоянно связана с данными, на основе которых она создана, и  обновляется автоматически при  изменении исходных данных. Более  того, изменение положения или размера элементов данных на диаграмме может привести к изменению данных на листе.

3.2. Построение с использованием Мастера диаграмм

      Выбор типа диаграммы. В диалоговом окне Мастер диаграмм (шаг 1 из 4): тип диаграммы следует выбрать тип и вид диаграммы. Диалоговое окно имеет две вкладки: Стандартные и Нестандартные.

       Во  вкладке Стандартные расположены стандартные типы диаграмм Microsoft Excel и их разновидности (виды) (рис. 4).

Таблица 8. Выбор типа и вида стандартной диаграммы 

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

Таблица 9. просмотр внешнего вида создаваемой диаграммы 

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

      По  окончании выбора типа и вида диаграммы  в диалоговом окне Мастер диаграмм (шаг 1 из 4): тип диаграммы следует нажать кнопку Далее.

      Выбор источника данных. В диалоговом окне Мастер диаграмм (шаг 2 из 4): источник данных диаграммы можно уточнить или  заново выбрать источник данных для  диаграммы. Диалоговое окно имеет две  вкладки: Диапазон данных и Ряд.

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

Таблица 10. Выбор источника данных 

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

Таблица 11. Выбор источника данных 

       Содержание  вкладки Ряд зависит от типа выбранной диаграммы, а также от выбора варианта построения рядов данных (рис. 12, рис. 13).

Таблица 12. выбор рядов данных и подписей делений оси Х: ряды данных в столбцах 

Таблица 13. Выбор рядов данных и подписей делений оси Х: ряды данных в строках 

       По  окончании работы с источниками  данных диаграммы в диалоговом окне Мастер диаграмм (шаг 2 из 4): источник данных диаграммы следует нажать кнопку Далее.

      Выбор основных параметров. В диалоговом окне Мастер диаграмм (шаг 3 из 4): параметры диаграммы можно выбрать и установить основные параметры диаграммы. Диалоговое окно, в зависимости от типа создаваемой диаграммы, имеет от трех до шести вкладок: Заголовки, Оси, Линии сетки, Легенда, Подписи данных, Таблица данных.

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

Таблица 14. Создание основных подписей в диаграмме 

      Во  вкладке Оси (рис. 15) в большинстве случаев ничего изменять не приходится. При желании можно, например, снять флажки ось Х и ось У и отказаться от осей на диаграмме. Это допустимо в тех случаях, когда на диаграмме отображаются подписи данных.

       Задать  параметры оси (максимальное и минимальное  значения, цену деления и др.) пока невозможно. Это можно будет сделать  при редактировании и оформлении оси. Вкладка Оси отсутствует  при создании круговых и кольцевых диаграмм.

Таблица 15. Выбор осей диаграммы 

      Во  вкладке Линии сетки (рис. 16) можно установить отображение линий сетки. Шаг линий сетки определяется ценой деления, установленной для соответствующей оси. Линии сетки облегчают восприятие величины данных на диаграмме. С другой стороны, установка всех линий сетки по всем осям затрудняет просмотр диаграммы. Для гистограмм обычно достаточно основных линий сетки по оси значений. Для графиков и точечных диаграмм полезно отобразить основные линии сетки и по оси категорий. Вкладка Линии сетки отсутствует при создании круговых и кольцевых диаграмм.

Таблица 16. установка линий сетки диаграммы 

      Во  вкладке Легенда (рис. 17) можно установить отображение легенды диаграммы и выбрать ее расположение. Наличие легенды желательно при создании диаграммы для нескольких рядов данных. При создании диаграмм для одного ряда данных или при наличии подписей на элементах данных диаграммы легенда не обязательна. Если в исходные данные диаграммы были включены имена рядов данных, подписи в легенде автоматически формируются из содержимого указанных ячеек, а затем автоматически изменяются при изменении содержимого ячеек. Если же имена рядов не были заданы, то в легенде будет отображен текст Ряд 1, Ряд 2 и т. д. В любом случае произвольно изменять текст легенды нельзя.

Таблица 17. Установка отображения легенды диаграммы 

      Во  вкладке Подписи данных (рис. 18) можно выбрать подписи, помещаемые на элементы данных диаграммы. Одновременно можно выбрать несколько подписей данных. В этом случае в раскрывающемся списке Разделитель можно выбрать символ, разделяющий записи. Содержание подписей данных автоматически формируется из содержимого соответствующих ячеек, а затем автоматически изменяется при изменении содержимого ячеек. Произвольно изменять содержимое подписей данных нельзя.

       Задать  параметры размещения подписей данных пока невозможно. Это можно будет  сделать при оформлении подписей.

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