Графические возможности Excel

Размещено на http://www.allbest.ru/

ЛАБОРАТОРНАЯ РАБОТА

1. ЦЕЛЬ РАБОТЫ

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

2. ТЕОРЕТИЧЕСКИЙ МАТЕРИАЛ

редактирование форматирование таблица диаграмма

2.1 Запуск Excel

Существует несколько способов запуска Excel. Ниже приведено описание наиболее удобных из них.

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

1. Щелкнуть на кнопке Пуск.

2. Установить курсор на пункт Программы и подождать немного, пока не появится вложенное меню.

3. Дальнейшие действия зависят от вида открывшегося меню:

a) если в нем есть строка Microsoft Excel, то необходимо щелкнуть на этой строке;

b) если в меню есть пункт Microsoft Office, то следует установить на него курсор, дождаться появления подменю, затем щелкнуть на строке Microsoft Excel.

Использование панели Microsoft Office

Если на экране имеется панель Microsoft Office, то запустить Excel можно щелчком на пиктограмме Excel.

С помощью ярлыка, расположенного на рабочем столе

Если на рабочем столе расположен ярлык Excel, то щелчок на ярлыке также запустит Excel.

При запуске Excel можно загрузить созданные и сохраненные ранее документы. Для этого нужно найти необходимый документ с помощью папки Мой компьютер или программы Проводник и дважды щелкнуть на имени документа. На рисунке 1 показан вид окна Excel.

2.2 Команды Excel

Все операции в Excel так же, как и в Word выполняются с помощью команд. Пользовательский интерфейс Excel предоставляет три способа вызова команд: с помощью меню, панелей инструментов и комбинаций клавиш. Некоторые команды можно вызывать, используя любой из этих методов. Например, команду Сохранить можно вызвать либо из меню Файл, либо щелкнуть на кнопке Сохранить Стандартной панели инструментов, либо воспользоваться комбинацией клавиш <Ctrl + S>. Но есть команды, которые можно вызвать только одним способом. К ним относится команда Сохранить как, которую можно вызвать только из меню Файл.

2.3 Панели инструментов

В Excel имеется более десяти панелей инструментов (Стандартная, Форматирования, Диаграммы и др.). Вывод панели инструментов на экран выполняется с помощью команды Вид => Панели инструментов (отмечая в открывшемся диалоговом окне Панели инструментов необходимые панели).

Существует быстрый способ отображения или скрытия панелей инструментов - щелкнуть правой кнопкой мыши на любой панели инструментов (но не на кнопках). В появившемся всплывающем меню - список всех имеющихся панелей инструментов. Чтобы отобразить или скрыть какую-либо панель инструментов, нужно щелкнуть на ее имени.

Как только панель инструментов появится на экране, можно изменить ее расположение. Для этого необходимо:

1) Установить указатель мыши на панель инструментов (но не на кнопке).

2) Нажать левую кнопку мыши и, не отпуская ее, двигать мышь в необходимом направлении.

Рис. 1. Елементы рабочего документа Еxcel

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

2.4 Основные понятия электронных таблиц

Рабочее поле электронной таблицы состоит из строк и столбцов. Максимальное количество строк/столбцов в Excel 98/2000 составляет - 256 / 65536. Каждое пересечение строки и столбца образует ячейку, в которую можно вводить данные (текст, число или формулу).

Номер строки (1,2,3 и т.д.) определяет ряд в электронной таблице.

Буква столбца (A…Z, AA…AZ, BA…BZ,…,ZA…ZZ) определяет колонку в электронной таблице.

Имя ячейки образуется из буквы столбца и номера строки, например В5.

Блок (область) - прямоугольная область смежных ячеек. Адрес блока состоит из координат противоположных углов - левого верхнего и правого нижнего, разделенных символом двоеточие (:), например А5:С12 (адрес блока может быть указан выделением на экране).

Электронная таблица имеет трехмерную структуру (книга). По умолчанию Excel присваивает документам имена Книга 1, Книга 2 и т.д. По умолчанию в Excel 97 и 2000 книга состоит из 3 листов. Для задания требуемого числа листов во вновь создаваемом документе следует воспользоваться командой Сервис => Параметры, вкладка Общие и задать необходимое значение в поле Листов в новой книге. На экране виден только один лист - верхний. Нижняя часть листа содержит ярлычки листов. Щелкая левой кнопкой мыши на ярлычке листа, можно перейти к соответствующему листу. Если ярлычок нужного листа не виден, то можно воспользоваться кнопками, средние кнопки выполняют переход на один лист назад или вперед, а крайние - к первому и последнему листам. Кроме того, комбинация клавиш <Ctrl + PageUp> выполняет переход к предыдущему листу, а <Ctrl + PageDown> - к следующему.

2.5 Управление документами Excel

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

2.5.1 Создание документов

После запуска Excel на экране появляется чистый рабочий лист (книга с установленными по умолчанию параметрами).

Создать документ можно также одним из указанных ниже способов:

- щелкнув на кнопке Создать Стандартной панели инструментов (при этом создается документ на основе шаблона Книга);

- нажав комбинацию <Ctrl + N> (используется шаблон Книга);

- с помощью команды Файл => Создать (рис. 2), выбрав базовый шаблон, на основании которого создается документ - по умолчанию Книга.xlt.

Рис. 2. Диалоговое окно Создание документа

2.5.2 Сохранение документов

Первоначальное сохранение нового либо сохранение существующего документа под другим именем, на другом диске, в другом каталоге выполняется командой Файл => Сохранить как (рис. 3).

При сохранении документа имя файлу пользователь задает самостоятельно, вводя его с клавиатуры в поле Имя файла, а расширение Excel присваивает в соответствии с выбранным пользователем из раскрывающегося списка Тип файла форматом сохраняемого документа (книга Microsoft Excel, шаблон и др.). Документы Excel сохраняются в виде файлов со стандартным расширением .XLS. После выбора имени и типа файла необходимо щелкнуть кнопку Сохранить диалогового окна Сохранение документа.

Рис. 3. Диалоговое окно Сохранение документа

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

Сохранение именованного файла выполняется командой Файл => Сохранить или с помощью кнопки Сохранить панели инструментов Стандартная либо комбинации клавиш <Ctrl + S>.

2.5.3 Открытие документов

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

В общем случае ранее сохраненный документ открывается для последующего использования с помощью команды Файл => Открыть, либо при использовании комбинации <Ctrl + О> (лат), либо с помощью соответствующей кнопки панели инструментов Стандартная.

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

Команда Файл => Параметры страницы, вкладка Страница задает формат и ориентацию печатной страницы.

Перед выводом документа на печать документ может просматриваться с помощью команды Файл => Просмотр или кнопки панели инструментов Стандартная.

Запустить документ на печать можно одним из следующих способов:

1) С помощью кнопки Печать панели инструментов Стандартная осуществляется непосредственный запуск печати документа с ранее установленными параметрами диалогового окна Печать.

2) Команда Файл => Печать вызывает диалоговое окно Печать, в котором задаются установки для печати документа.

2.6 Изменение параметров страницы

Команда Файл | Параметры страницы вызывает одноименное диалоговое окно (рис. 4), в котором осуществляется задание основных параметров:

вкладка Страница - выбор формата (размера) и ориентации печатной страницы (книжная - по вертикали, альбомная - по горизонтали) ;

вкладка Поля - установка размеров полей, отступов колонтитулов от верхнего и нижнего края печатной страницы;

вкладка Колонтитулы - указание текста колонтитулов;

вкладка Лист - определение последовательности вывода страниц; указание диапазона ячеек, выводимых на печать; печатать или нет сетку и т.д.

Рис. 4. Диалоговое окно Параметры страницы

2.7 Выделение элементов таблицы

Для выделения с помощью левой кнопки мыши:

- столбца - щелкнуть на бордюре на букве - имени столбца;

- нескольких столбцов - не отпуская кнопку, протянуть мышь;

- строки - щелкнуть на бордюре на числе - номере строки;

- нескольких строк - не отпуская кнопку, протянуть мышь;

- блока - щелкнуть на начальной ячейке и, не отпуская кнопку мыши, протянуть на последнюю;

- нескольких несмежных блоков - выделить первую ячейку (или блок), а затем, удерживая клавишу <Ctrl>, выделить все остальные ячейки (или блоки);

- рабочего листа - щелкнуть в ячейке на бордюре на пересечении всех столбцов и строк (выше строки с номером 1 и левее столбца А).

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

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

2.8 Содержимое ячеек

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

Ввод текста

Текст - набор любых символов.

Текст, начинающийся с числа, необходимо вводить после символа «`» (апостроф). Например, введите в ячейку А1 число 001234, нажмите клавишу <Enter>. В ячейку записано число 1234. Чтобы число сохранилось в виде 001234, его следует ввести как текст, т.е. `001234.

Если ширина текста больше ширины ячейки и ячейка справа пуста, то текст на экране займет и ее место. При вводе данных в соседнюю ячейку предыдущий текст на экране будет обрезан, а в памяти он сохранится полностью. Например, введите в ячейку А2 текст Мы изучаем компьютерную технику, а затем в ячейку В2, введите текст 2 семестр. При этом часть текста Мы изучаем компьютерную технику будет обрезана. Чтобы увидеть текст в ячейке А1 целиком, нужно установить указатель мыши на разделительную линию бордюра между буквами столбцов А и В (при этом курсор принимает вид двунаправленной стрелки ), нажать левую кнопку мыши и переместить мышь вправо.

Для удаления набранных примеров нужно выделить диапазон ячеек А1:В2 и нажать на клавиатуре клавишу <Delete>.

Ввод чисел

Числа можно вводить со знаками =, +, - или без них. При вводе вещественных чисел в русифицированных версиях Excel разделителем между целой и дробной часть является «,» (запятая), например 12,25. В Excel имеется несколько форматов представления чисел: денежный, процентный научный, даты и др. (рис. 5).

Примеры форматов
Числовой с одним десятичным знаком	Числовой с двумя десятичными знаками	Денежный с двумя десятичными знаками и обозначением грн.	Дата
0,2	0,20	5,00 грн.	12 май 67
0,4	0,45	267,50 грн.	2 май 00
0,6	12,00	17,58 грн.	21 авг 03
0,8	-5,00	345455656,89 грн.	5 янв 05

Рис. 5. Примеры числовых форматов в Excel

Форматы могут задаваться либо в явном виде при вводе, либо для выделенных ячеек - командой Формат => Ячейки, вкладка Число. Например, выделите ячейки С3:С6; выберите команду Формат => Ячейки, вкладка Число; задайте те же параметры, что на рис. 6; нажмите кнопку Ok; в ячейке С3 наберите 5; нажмите <Enter> (при этом курсор перемещается на ячейку С4); наберите 267,5; нажмите <Enter> (при этом курсор перемещается на ячейку С5); наберите 17,575; нажмите Enter (при этом курсор перемещается на ячейку С6); наберите 345455656,89; нажмите <Enter>. Данные в ячейках С3:С6 представлены, как на рис. 5.

Если длина числа больше ширины ячейки, то:

- либо отображается число в экспоненциальном формате. Например, наберите в ячейке D1 число 123456789123456789 и нажмите клавишу <Enter>, в ячейке отображается значение 1,23456Е+17, что соответствует 1,2345617;

- либо - символы #####. В этом случае надо изменить ширину ячейки, как в примере с текстом Мы изучаем компьютерную технику.

Для удаления набранных примеров нужно выделить диапазон ячеек С3:D6 и нажать на клавиатуре клавишу <Delete>.

Рис. 6. Окно Формат ячеек, вкладка Число

Excel предоставляет возможность автозаполнения при вводе закономерных числовых последовательностей. Например, в ячейку А1 введите число 2; нажмите <Enter> (при этом курсор перемещается на ячейку А2); введите число 4; выделите ячейки А1:А2; совместите курсор с правым нижним углом блока так, чтобы курсор принял вид черного плюса (маркера заполнения); нажмите левую кнопку мыши и, не отпуская, потяните до ячейки А4; отпустите кнопку мыши. В ячейке А3 появляется число 6, в А4 - 8. Аналогично, можно ввести в В1 - январь; в В2 - февраль; выделить В1:В2 и заполнить до В4. В ячейке В3 появляется март, в В4 - апрель.

Ввод формул

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

2.9 Способы адресации в Excel

В Excel имеется три способа адресации (обращения к ячейкам): абсолютная, относительная и смешанная.

Примеры способов адресации.

Пример 1. Относительная адресация.

Введите в ячейки А1, А2, А3, В1, В2, В3 числа 10, 20, 30, 5, 10, 15, как показано на рис. 7.

Установите курсор в ячейку С1, наберите с помощью клавиатуры =, затем щелкните левой кнопкой мыши ячейку А1, наберите с помощью клавиатуры / и щелкните левой кнопкой мыши ячейку В1, нажмите клавишу <Enter>. В ячейке С1 появляется число 2, как на рисунке 7,б.

а) формульный вид

б) расчетный вид

Рис. 7. Способы адресации в Excel

Установите курсор на ячейку С1, совместите курсор с правым нижним углом ячейки так, чтобы курсор принял вид черного плюса (маркера заполнения); нажмите левую кнопку мыши и, не отпуская, потяните до ячейки C3; отпустите кнопку мыши. В ячейке C2 появляется число 2, в C3 - 2.

Для просмотра таблицы в формульном виде нужно командой Сервис => Параметры вызвать окно Параметры (рис. 8) и на вкладке Вид в группе Параметры окна установить флажок формулы. Для возврата к расчетному виду нужно командой Сервис => Параметры вызвать окно Параметры и на вкладке Вид в группе Параметры окна убрать флажок формулы.

При относительной адресации в формулах Excel запоминается расположение относительно текущей ячейки. В данном примере содержимое ячейки С1 интерпретируется как "разделить содержимое ячейки, расположенной двумя рядами левее, на содержимое ячейки, расположенной на один ряд левее". Поэтому при копировании в С2 записывается формула =А2/В2, а С3 - =А3/В3.

Пример 2. Абсолютная адресация

Установите курсор в ячейку D1, наберите с помощью клавиатуры =, затем щелкните левой кнопкой мыши ячейку А1, наберите с помощью клавиатуры / $В$1, нажмите клавишу <Enter>. В ячейке D1 появляется число 2 (рис. 7,б).

Установите курсор на ячейку D1, совместите курсор с правым нижним углом ячейки так, чтобы курсор принял вид черного плюса (маркера заполнения); нажмите левую кнопку мыши и, не отпуская, потяните до ячейки D3; отпустите кнопку мыши. В ячейке D2 появляется число 4, в D3 - 6.



Рис. 8. Окно Параметры, вкладка Вид

Для просмотра таблицы в формульном виде нужно командой Сервис => Параметры вызвать окно Параметры (рис. 8) и на вкладке Вид в группе Параметры окна установить флажок формулы. Для возврата к расчетному виду нужно командой Сервис => Параметры вызвать окно Параметры и на вкладке Вид в группе Параметры окна убрать флажок формулы.

При абсолютной адресации перед именем столбца и номером строки указывается символ $. Абсолютная адресация является ссылкой на конкретную ячейку.

При смешанной адресации символ $ ставится только перед именем столбца или номером строки, например, $В1. В этом случае изменяется только один параметр адреса.

2.10 Форматирование и оформление ячеек

Шрифтовое оформление ячеек может быть выполнено командой Формат| Ячейки, вкладка Шрифт или кнопками



панели инструментов Форматирование.

Выравнивание

Содержимое любой ячейки можно выровнять по левому или правому краю, по центру (горизонтали и вертикали), а также задать необходимую ориентацию текста (снизу вверх, сверху вниз и т.д.). Указанные действия можно выполнить командой Формат => Ячейки, вкладка Выравнивание или соответствующими кнопками панели инструментов Форматирование.

Рамки таблицы задаются либо командой Формат | Ячейки, вкладка Граница, либо кнопкой панели инструментов Форматирование.

Содержимое ячейки (блока) может иметь необходимый фон (тип штриховки, цвет штриховки, цвет фона). Фон задается либо командой Формат|Ячейки, вкладка Фон, либо кнопкой панели инструментов Форматирование.

2.11 Редактирование таблицы

2.11.1. Изменение ширины столбцов и высоты строк

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

2.11.2 Редактирование содержимого ячеек

Во время ввода данных для редактирования могут использоваться клавиши:

- для удаления символа, расположенного перед курсором, - <Backspace> (¬);

- для удаления символа, расположенного после курсора, - <Delete>;

- для удаления всех данных из ячейки - <Esc>.

Для редактирования содержимого ячеек после завершения ввода необходимо установить курсор на редактируемую ячейку и затем:

- либо отредактировать данные в строке формул;

- либо нажать F2 и отредактировать данные в самой ячейке;

- либо дважды щелкнуть ячейку и отредактировать данные в самой ячейке.

Окончание редактирования - нажатие клавиши <Enter>, либо нажатие клавиши перемещения курсора, либо щелчок левой кнопкой мыши по другой ячейке.

2.11.3 Перемещение содержимого ячеек

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

1. Выделить ячейку или блок.

2. Выбрать команду Правка | Вырезать.

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

4. Выбрать команду Правка | Вставить.

2.11.4 Копирование содержимого ячеек

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

1. Выделить ячейку или блок.

2. Выбрать команду Правка | Копировать.

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

4. Выбрать команду Правка | Вставить.

2.11.5 Удаление содержимого ячеек

Для удаления содержимого ячеек можно выделить ячейки и нажать клавишу <Delete>.

2.12 Использование функций в формулах

В состав Excel входит свыше 300 функций.

Для добавления функции в формулу можно:

- либо набрать ее имя на клавиатуре, например, продолжим таблицу рис. 8, установим курсор на ячейку Е1 и введем с клавиатуры =МАКС(А1:D1), нажмем <Enter>. В ячейке Е1 появляется значение 10 (функция МАКС описана ниже);

- либо воспользоваться командой Вставка => Функция;

- либо щелкнуть по кнопке панели инструментов Стандартная.

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

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

Рассмотрим некоторые функции.

2.12.1 Функция ЕСЛИ

Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

Функция ЕСЛИ используется для условной проверки значений и формул и имеет следующий синтаксис:

ЕСЛИ(лог_выражение;значение_если_истина;

Рис. 9. Окно мастера функций, 1 шаг

Рис. 10. Окно мастера функций, 1 шаг

значение_если_ложь)

лог_выражение - это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

значение_если_истина - это значение, которое возвращается, если лог_выражение имеет значение ИСТИНА. Если лог_выражение имеет значение ИСТИНА и значение_если_истина опущено, то возвращается значение ИСТИНА. Значение_если_истина может быть другой формулой.

Значение_если_ложь - это значение, которое возвращается, если лог_выражение имеет значение ЛОЖЬ. Если лог_выражение имеет значение ЛОЖЬ и значение_если_ложь опущено, то возвращается значение ЛОЖЬ. Значение_если_ложь может быть другой формулой.

Пример 1

В примере, если значение ячейки A10 - 100, то лог_выражение имеет значение ИСТИНА и вычисляется сумма для ячеек B5:B15. В противном случае лог_выражение имеет значение ЛОЖЬ и возвращается пустой текст (""), очищающий ячейку, которая содержит функцию ЕСЛИ.

ЕСЛИ(A10=100;СУММ(B5:B15);"")

Пример 2

Предположим, что рабочий лист по расходам содержит в ячейках B2:B4 фактические расходы за январь, февраль, март: 1500, 500 и 500 соответственно. Ячейки C2:C4 содержат данные по предполагаемым расходам за те же периоды: 900, 900 и 925.

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

ЕСЛИ(B2>C2;"Превышение бюджета";"OK")

равняется "Превышение бюджета".

ЕСЛИ(B3>C3;"Превышение бюджета";"OK")

равняется "OK".

2.12.2 Функция МАКС

Возвращает наибольшее значение из набора значений.

Синтаксис:

МАКС(число1;число2; ...)

число1, число2, ...- это от 1 до 30 чисел, среди которых ищется максимальное значение.

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

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

Если аргументы не содержат чисел, то функция МАКС возвращает 0 (ноль).

Пример 1

Если ячейки A1:A5 содержат числа 10, 7, 9, 27 и 2, то:

МАКС(A1:A5)

равняется 27

Пример 2

МАКС(A1:A5;30)

равняется 30

2.12.3 Функция МИН

Возвращает наименьшее значение в списке аргументов.

Синтаксис:

МИН(число1;число2; ...)

Число1, число2, ...- это от 1 до 30 чисел, среди которых ищется минимальное значение.

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

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

Если аргументы не содержат чисел, то функция МИН возвращает 0.

Пример 1

Если A1:A5 содержит числа 10, 7, 9, 27 и 2, то:

МИН(A1:A5)

равняется 2

Пример 2

МИН(A1:A5; 0)

равняется 0

2.12.4 Функция СРЗНАЧ

Возвращает среднее (арифметическое) своих аргументов.

Синтаксис:

СРЗНАЧ(число1; число2; ...)

Число1, число2, ...- это от 1 до 30 аргументов, для которых вычисляется среднее.

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

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

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

Пример 1

Если ячейки A1:A5 имеют имя Баллы и содержат числа 10, 7, 9, 27 и 2, то:

СРЗНАЧ(A1:A5) равняется 11

СРЗНАЧ(Баллы) равняется 11

СРЗНАЧ(A1:A5; 5) равняется 10

СРЗНАЧ(A1:A5) равняется СУММ(A1:A5)/СЧЁТ(A1:A5) и равняется 11

Если ячейки C1:C3 имеют имя ДругиеБаллы и содержат числа 4, 18 и 7, то:

СРЗНАЧ(Баллы; ДругиеБаллы) равняется 10,5

2.12.5 Функция СУММ

Суммирует все числа в интервале ячеек.

Синтаксис:

СУММ(число1;число2; ...)

Число1, число2, ... - это от 1 до 30 аргументов, для которых требуется определить итог или сумму.

Учитываются числа, логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов (первый и второй примеры).

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

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

Пример 1

СУММ(3; 2) равняется 5.

Пример 2

СУММ("3"; 2; ИСТИНА) равняется 6, так как текстовые значения преобразуются в числа, а логическое значение ИСТИНА преобразуется в число 1.

Если ячейка A1 содержит "3", а ячейка B1 содержит ИСТИНА, то:

СУММ(A1; B1; 2) равняется 2, так как нечисловые значения в ссылке не преобразуются.

Пример 3

Если ячейки A2:E2 содержат числа 5, 15, 30, 40 и 50, то:

СУММ(A2:C2) равняется 50

СУММ(B2:E2; 15) равняется 150.

2.12.6 Функция СУММЕСЛИ

Суммирует ячейки, специфицированные заданным критерием.

Синтаксис:

СУММЕСЛИ(интервал; критерий;сумм_интервал)

Интервал это интервал вычисляемых ячеек.

Критерий это критерий в форме числа, выражения или текста, который определяет, какая ячейка добавляется. Например, критерий может быть выражен как 32, "32", ">32", "яблоки".

Сумм_интервал это фактические ячейки для суммирования. Ячейки в сумм_интервал суммируются, только если соответствующие им ячейки в аргументе интервал удовлетворяют критерий. Если сумм_интервал опущен, то суммируются ячейки в аргументе интервал.

Пример

Пусть ячейки A1:A4 содержат следующие величины стоимости для четырех домов: 100000 грн., 200000 грн., 300000 грн., 400000 грн. соответственно. Пусть ячейки B1:B4 содержат следующие величины комиссионных при продаже соответствующих домов: 7000 грн., 14000 грн., 21 000 грн., 28 000 грн.

СУММЕСЛИ(A1:A4;">160000";B1:B4) равняется 63 000 грн.

2.12.7 Функция СЧЕТ

Подсчитывает количество чисел в списке аргументов. Функция СЧЁТ используется для получения количества числовых ячеек в интервалах или массивах ячеек.

Синтаксис:

СЧЁТ(значение1; значение2; ...)

Значение1, значение2, ... - это от 1 до 30 аргументов, которые могут содержать или ссылаться на данные различных типов, но в подсчете участвуют только числа.

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

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

Пример

Для таблицы вида

СЧЁТ(A1:A7) возвращает 3

СЧЁТ(A4:A7) возвращает 2

СЧЁТ(A1:A7, 2) возвращает 4

2.12.8 Функция СЧЕТЕСЛИ

Подсчитывает количество ячеек внутри интервала, удовлетворяющих заданному критерию.

Синтаксис:

СЧЁТЕСЛИ(интервал;критерий)

Интервал - это интервал, в котором нужно подсчитать ячейки.

Критерий - это критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки".

Пример 1

Пусть ячейки A3:A6 содержат "яблоки", "апельсины", "персики", "яблоки" соответственно. СЧЁТЕСЛИ(A3:A6;"яблоки") равняется 2.

Пример 2

Пусть ячейки B3:B6 содержат 32, 54, 75, 86 соответственно. СЧЁТЕСЛИ(B3:B6;">55") равняется 2.

Приведем пример использования некоторых из описанных выше функций (рис. 11).

Рис. 11. Пример использования функций

Наберите таблицу, представленную на рис. 11. Надпись «Наименование товара» выполните следующим образом: в ячейке А2 наберите Наименование товара; нажмите <Enter>; выделите ячейки А2:А3; выполните команду Ячейки => Формат; в открывшемся окне Форматирование ячеек выберите вкладку Выравнивание и установите на ней флажки Объединение ячеек и Переносить по словам; нажмите Ok.

Надпись «Реквизиты продавца» выполните следующим образом: выделите ячейки В2:С2; выполните команду Ячейки => Формат; в открывшемся окне Форматирование ячеек выберите вкладку Выравнивание и установите на ней флажок Объединение ячеек; выравнивание по горизонтали - по центру; выравнивание по вертикали - по центру. Аналогично выполняется надпись «Расчеты». Для заданных в таблице данных в ячейках записаны формулы для следующих расчетов:

- в В12 - сколько фактов реализации продавцом Иванов отмечено

=СЧЁТЕСЛИ(B4:B10;"Иванов")

- в D12 - максимальная цена

=МАКС(D4:D10)

- в Е12 - общее количество товара компьютер

=СУММЕСЛИ(A4:A10;"компьютер";Е4:Е10)

2.13 Графические возможности Excel

2.13.1 Построение диаграмм

Диаграммы служат для наглядного представления табличных данных.

Рассмотрим построение диаграммы на примере по данным таблицы рис.11.

Пример

Построить круговую диаграмму по данным столбца «Количество» относительно данных столбца «Наименование товара». Легенда - данные столбца «Наименование». Значения областей диаграммы должны быть выражены в процентах.

Для построения диаграммы необходимо:

1) Выделить диапазон ячеек, по которым будет построена диаграмма. В данном случае необходимо выделить ячейки В3:В10, а затем, удерживая нажатой клавишу <CTRL>, выделить F3:F10.

2) Щелкнуть в панели инструментов Стандартная кнопку или вызвать команду Вставка => Диаграмма.

3) В открывшемся окне Мастер диаграмм (шаг 1 из 4) на вкладке Стандартные выбрать тип диаграммы Круговая и понравившийся вид; щелкнуть по кнопке окна Далее> (рис. 12).

4) В открывшемся окне Мастер диаграмм (шаг 2 из 4) проверить, правильно ли указан Диапазон (должен быть В3:В10; F3:F10); переключатель Ряды в: должен находиться в положении столбцах. Щелкнуть по кнопке окна Далее> (рис. 13).

5) В открывшемся окне Мастер диаграмм (шаг 3 из 4):

- на вкладке Заголовки в поле «Название диаграммы» можно ввести название диаграммы (в примере введено «Круговая диаграмма»);

- на вкладке Легенда можно задать параметры легенды (пояснений по обозначениям);

- на вкладке Подписи данных задать параметры подписей данных; в примере в группе Подписи значений установлен переключатель Доля (рис. 14).

Щелкнуть по кнопке окна Далее>.

Рис. 12. Окно Мастер диаграмм (шаг 1 из 4)



Рис. 13. Окно Мастер диаграмм (шаг 2 из 4)

Рис. 14. Окно Мастер диаграмм (шаг 3 из 4)

Рис. 15. Окно Мастер диаграмм (шаг 4 из 4)

6) 6. В открывшемся окне Мастер диаграмм (шаг 4 из 4): установить переключатель Поместить диаграмму на листе: в нужное положение и щелкнуть по кнопке окна Готово (рис. 15).

2.13.2 Редактирование диаграммы

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

- либо щелкнуть по ней правой кнопкой мыши (например, на заголовке - рис. 16). В открывшемся контекстном меню щелкнуть левой кнопкой мыши команду Формат... Открывается окно форматирования выбранной области;

- либо дважды щелкнуть левой кнопкой мыши объект, который необходимо отредактировать.

Рис. 16. Форматирование области заголовка

3. ЗАДАНИЯ К ЛАБОРАТОРНОЙ РАБОТЕ

Задание №1 к лабораторной работе

Задача 1

1) Составить таблицу (рис. 17) с указанными ниже параметрами:

- наименование таблицы выполнить методом объединения ячеек по центру;

- шапка таблицы и итоговая строка должны быть выделены жирным обрамлением;

- заголовки Наименование товара, Реквизиты продавца и Расчеты должны быть оформлены методом объединения ячеек с центрированием по горизонтали;

- ячейки под заголовками столбцов Цена, Доход, Расход, Прибыль должны иметь денежный формат с числом знаков после запятой 2 и обозначением грн.;

- ячейки под заголовками столбцов № счета и Кол-во должны иметь числовой формат;

- остальные ячейки должны иметь текстовый формат.

Рис. 17. Вид таблицы

2) Рассчитать ячейки под заголовками столбцов Доход, Расход, Прибыль и итоговую строку:

- доход рассчитывается как произведение Цены и Количества;

- Расход равен 42% от Дохода;

- Прибыль рассчитывается как разность Дохода и Расхода;

- итоговая строка рассчитывается по столбцам Доход, Расход и Прибыль.

3) Построить кольцевую диаграмму по данным столбца Прибыль относительно данных столбца Наименование товара. Легенда - данные столбца Наименование товара. Значения областей диаграммы должны быть выражены в процентах.

Задача 2

Рассчитать заработную плату десяти работников отдела, исходя из следующих данных: почасовая ставка для работников в зависимости от категорий следующая - 3-я категория - 5,9 грн./час; 2-я категория - 12,4 грн./час; 1-я категория - 25,5 грн./час.

В отделе работают пять работников третьей категории, три работника второй категории и два работника первой категории. Один из работников первой категории (Павлов В.В.) отработал 80 часов, а другой (Заморин А.Р.) - 100 часов. Два работника второй категории (Иванов Ю.П. и Петров А.А.) отработали в месяц 120 часов каждый, а третий - Ступин А.В. - 50 часов. Работники третьей категории отработали соответственно: Сидоров П.Л. - 140, Дюков А.А. - 105, Панин Р.А. - 105, Козлов С.П. - 80, Салин Е.Л. - 110 часов.

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

Задача 3

Рассчитать среднюю плату за пребывание ребенка в детском саду в 2001, 2002, 2003 гг., учитывая, что по месяцам она составила: 2001 г. - с 1.01 по 30.04 - 8 грн. за месяц, с 1.05 по 31.08 - 15 грн. за месяц, с 1.09 по 31.12 - 40 грн. за месяц; 2002 г. - с 1.01 по 31.08 - 55 грн. за месяц, с 1.09 по 31.12 - 70 грн. за месяц; 2003 г. - с 1.01 по 31.07 - 90 грн. за месяц; с 1.08 по 31.12 - 125 грн. за месяц.

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

Задача 4

Рассчитать плату за электроэнергию в 2003 и в 2004 годах, если норма в 2003 г. составила 0,11 грн. за киловатт, а в 2004 г. - 0,156 грн. за киловатт.

Расход электроэнергии жильцами восьмиквартирного дома составил: квартира 1 - 120 Квт/мес., квартира 2 - 80 Квт/мес., квартира 3 - 150 Квт/мес., квартира 4 - 200 Квт/мес., квартира 5 - 120 Квт/мес., квартира 6 - 75 Квт/мес., квартира 7 - 160 Квт/мес., квартира 8 - 270 Квт/мес.

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

Задача 5

Рассчитать зарплату работников предприятия в зависимости от отработанного объема часов, если общая сумма выделенная на зарплату составила 25 тысяч грн., подоходный налог составляет 13% от начисленной суммы, 1% составляют выплаты в пенсионный фонд, 2% составляют прочие выплаты.

В отделе 7 работников, отработавшие соответственно: Петров А.Л., - 180 часов, Петров Л.Д. - 20 часов, Сидоров Е.Н. - 80 часов, Павлов А.А. - 120 часов, Лаптев П.Л. - 90 часов, Ванин П.П. - 120 часов, Калмыков М.И. - 100 часов.

Расчет представить в виде электронной таблицы и вывести сравнительную диаграмму зарплаты (тип диаграммы выбрать самостоятельно).

Задача 6

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

№ п/п	Статьи затрат	Примечание
1	Стоимость вычислительной техники
2	Стоимость прочего оборудования	10% от п.1
3	Монтаж и наладка ВТ	10% от п.1
4	Монтаж и пусконаладочные работы вспомогательного оборудования	5% от п.1
5	Среднегодовая заработная плата,
	в том числе: основная
	дополнительная	8% от осн. з/п
6	Амортизацонные отчисления на ВТ	12% от п.1
7	Стоимость ремонта оборудования	2,5% от п.1
8	Возмещение износа быстроизнашиваемых предметов	2% от п.1
9	Стоимость электроэнергии
10	Транспортные расходы	4% от п.1
11	Прочие расходы	2% от п.1

Представить в виде объемной гистограммы соотношение затрат по указанным пунктам.

Задача 7

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

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

Форма таблицы

ПРИБЫЛЬ В ЗАВИСИМОСТИ ОТ ОБЪЕМА РЕАЛИЗАЦИИ

Отпускная цена за единицу товара $7,5

Переменные издержки на единицу продукции $2,5

Размер партии, щтук	Постоянные издержки, $US	Переменные издержки, $US	Общие издержки, $US	Доход от продаж, $US	Прибыли и убытки, $US
50	1250
100	1250
150	1250

Задача 8

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

Таблица должна рассчитывать итоговые издержки за месяц и средний пробег (в км/литр).

Форма таблицы

РАСХОДЫ НА СЛУЖЕБНЫЕ МАШИНЫ

Месяц июль

Число месяца	Отсчет спидометра, км	Куплено бензина, л	Цена за 1 л	Стоимость бензина	Допол. расходы	Общая стоимость	Пробег, км
1	39800	40	9,8
5	40100	20	9,8		50000
10	40300	0	9,8
15	40420	40	9,8		70000
20	40530	20	9,8
25	40680	40	9,8
30	40700	0	9,8

Итого к оплате:___________

Средний пробег, км/л:_____

Задача 9

Составить таблицу прейскурантных цен. Она должна содержать сведения о коде изделия, цене нетто, НДС (рассчитывается по ставке НДС), цене с НДС, цене изделия с учетом 10% и 15% скидок постоянным клиентам фирмы.

Форма таблицы

ПРЕЙСКУРАНТНЫЕ ЦЕНЫ С УЧЕТОМ НДС И СКИДОК ПОСТОЯННЫМ КЛИЕНТАМ ФИРМЫ

Ставка НДС 20%

Код изделия	Цена нетто	Общая стоимость	Скидка 10%	Цена со скидкой 10%	Скидка 15%	Цена со скидкой 15%
Y340	$20
Y341	$31
Y342	$14
Y343	$22
Y344	$25
Y345	$31
Y346	$39
Y347	$28

Задание №2 к лабораторной работе

1) Составить таблицу (приложение 1), параметры таблицы должны быть максимально приближены к указанным на рисунке.

2) Рассчитать сумму прихода, сумму продажи, прибыль и рентабельность по всем строкам.

3) Вычислить среднюю рентабельность и среднюю прибыль.

4) Вычислить максимальную и минимальную рентабельность.

5) Сколько фактов реализации сыпучих материалов отмечено?

6) На какую сумму реализовано цемента?

7) На какую сумму реализовано цемента белого?

8) Какова суммарная прибыль товаров, продававшихся по цене >200грн. за единицу?

9) Какова суммарная прибыль, полученная при реализации сыпучих, отделочных, лакокрасочных материалов и пиломатериалов?

10) Построить график по результатам выполнения задания. Вид графика - гистограмма. Оформление графика выбрать самостоятельно.

11) Рассчитать среднюю рентабельность, только тогда, когда сумма продажи была больше 100 грн.

12) Рассчитать количество фактов реализации товаров, пришедших в количестве более 100 единиц.

13) Вычислить суммарную прибыль товаров, продававшихся в ноябре.

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

КОНТРОЛЬНЫЕ ВОПРОСЫ

1) Как запустить табличный процессор Excel?

2) Опишите последовательность действий, необходимых для выделения блока, нескольких несмежных блоков.

3) Какие данные могут заноситься в ячейки таблицы?

4) Как задать для ячейки (блока ячеек) формат числовых данных?

5) Как выполняется ввод формул в ячейки?

6) Как выполняется заполнение (копирование вправо или влево, вверх или вниз)?

7) Назовите способы адресации в Excel. Приведите примеры использования абсолютной, относительной и смешанной адресации.

8) Как выполнить печать таблицы на экране (предварительный просмотр) и на принтере?

9) Опишите последовательность построения диаграмм.

10) Из каких элементов состоит диаграмма, как выполняется редактирование отдельных элементов диаграммы?

11) Синтаксис функций Excel.

12) Работа с мастером функций Excel.

13) Для чего применяется функция EСЛИ и какой ее синтаксис? Приведите примеры использования функции ЕСЛИ.

14) Для чего применяют функции МАКС и МИН и каков их синтаксис? Приведите примеры использования функций МАКС и МИН.

15) Для чего применяют функции СУММ и СУММЕСЛИ? В чем отличие функции СУММЕСЛИ от функции СУММ. Какой синтаксис у функции СУММЕСЛИ? Приведите примеры использования функции СУММЕСЛИ.

16) Для чего применяют функции СЧЁТ и СЧЁТЕСЛИ? В чем отличие функции СЧЁТЕСЛИ от функции СЧЁТ. Какой синтаксис у функции СЧЁТЕСЛИ? Приведите примеры использования функции СЧЁТЕСЛИ.

Размещено на Allbest.ur

...