Logo GenDocs.ru

Поиск по сайту:  

Загрузка...

Лекции по Microsoft Excel 2003 - файл Конспект лекций по Excel.doc


Лекции по Microsoft Excel 2003
скачать (393.1 kb.)

Доступные файлы (1):

Конспект лекций по Excel.doc1237kb.10.09.2007 12:16скачать

содержание

Конспект лекций по Excel.doc

  1   2   3   4   5   6   7   8

Источник: Быков В. Л., Ашаев Ю. П. Основы информатики. Пособие. – Брест, Издательство БрГТУ, 2006

Раздел 6

6. Электронная таблица Excel

6.1. Основные сведения


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

Для загрузки программы запустите программу Windows и щелкните мышкой по значку приложения Excel в панели инструментов Microsoft Office.

Для выхода из программы введите команду Файл, Выход.
^

Описание рабочего окна Excel


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

Строка меню  обеспечивает доступ ко всем командам электронной таблицы.

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

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

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

Строка формул имеет три поля: поле адреса ячейки, поле управляющих клавиш и поле ввода данных. В поле ^ Адреса ячейки указан адрес текущей ячейки или ее имя. Если щелкнуть мышкой по этому полю, ввести адрес ячейки и нажать клавишу Enter, то курсор электронной таблицы перейдет в указанную ячейку. Этот прием перехода к нужной ячейке называется непосредственной адресацией. В поле управляющих кнопок выводятся три кнопки: Х - отмена редактирования строки ввода,  - окончание редактирования, “=” – ввод символа “=”. Названные кнопки появляются при активизации строки ввода. Поле Ввода данных предназначено для отображения вводимой информации или содержания выделенной ячейки. Для активизации строки ввода необходимо щелкнуть по ней мышью.

Строка состояния расположена в нижней части рабочего окна. В левой части строки состояния выводится информация о текущем состоянии электронной таблицы. В правой части выводится информация о состоянии управляющих клавиш Caps Lock и NumLock.
^

Рабочая книга, рабочий лист


Информация в электронной таблице сохраняется в виде рабочих книг. Имя книги выводится в строке заголовка. Рабочая книга состоит из листов различного типа. Максимально возможное число листов в рабочей книге - 256.

Рабочий лист состоит из пронумерованных строк и столбцов. Столбцы рабочих листов озаглавлены латинскими буквами от A до Z и их комбинациями, например АА, АВ, IU, IV. Строки пронумерованы цифрами. Рабочий лист может содержать до 256 столбцов и до 16384 строк. На пересечении строк и столбцов образованы ячейки. В одной из ячеек расположен контур выделения - курсор электронной таблицы. Рабочий лист имеет номер, который указан на ярлыке (рис. 6.1). Если щелкнуть правой кнопкой мыши по ярлыку, то отроется контекстное меню с перечнем команд для управления рабочим листом. Рабочие листы можно добавлять, удалять, копировать, переименовывать, перемещать, группировать, разгруппировывать.

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

Ячейка


Основным элементом таблицы является ячейка.

Ячейка - область, образованная пересечением строки и столбца. Она обозначается номером столбца и строки, на пересечении которых находится. Например, А1, IV9999.

Диапазон (группа, блок) - непрерывная область ячеек, обозначенная номерами начальной и конечной ячеек, разделенных двоеточием или точкой, например, А1:С10, D8.H12. Ячейке или диапазону может быть присвоено уникальное имя.

Ячейка характеризуется следующими параметрами: адрес, содержание, значение, формат, статус.

Адрес ячейки может быть абсолютным, относительным и смешанным: относительный адрес: А1, Е7; абсолютный адрес: $A$1, $E$7; смешанный адрес: $A1, A$1. Абсолютный адрес ячейки не меняется в операциях копирования, вставки или удаления ячеек, строк и столбцов. Если ячейке присвоен смешанный адрес, то при копировании будет меняться только тот параметр, перед которым не стоит знака $. Например: $D6 - при копировании ячейки будет меняться только номер строки; D$5 – при копировании будет меняться только адрес столбца.

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

    1. Выделить ячейку (диапазон ячеек);

    2. Ввести команду Вставка, Имя, Присвоить;

    3. Ввести в строке ввода диалогового окна имя ячейки и щелкнуть ОК.

Для удаления имени ячейки введите команду Вставка, Имя, Присвоить выделите в диалоговом окне имя удаляемой ячейки, щелкните команду Удалить и ОК.

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

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

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

Число может быть представлено в виде целого числа (123), вещественного числа с десятичной точкой (0,0001785) или в экспоненциальной форме (1,785E-4). Дробная часть числа отделяется от целой части запятой.

^ Текстовая константа - это сама константа. Текстовая константа - строка символов длиной до девяти знаков, используется в выражениях как операнд, при вводе текстовой константы она заключается в скобки и в кавычки, например, ("ноябрь").

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

^ Сообщения об ошибках:

#ДЕЛ/0! - деление на ноль;

#ИМЯ? - не определено имя переменной в формуле;

#Н/Д! - нет допустимых значений, аргумент функции не может быть определен;

#ПУСТО! - итога не существует;

#ЧИСЛО! - избыточное число, либо неверное использование числа, например, КОРЕНЬ(-1);

#ССЫЛКА! - неверная ссылка; ячейка, на которую она сделана, в рабочем листе не существует;

#ЗНАЧ! - неправильный тип аргумента; например, использование текста там, где необходимо число.

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

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

Ширина ячейки может быть от 1 до 127 символов, по умолчанию - 9 символов.

^ Режим отображения формул: формула или значение. По умолчанию - значение. Для перехода к режиму отображения формул необходимо ввести команду Сервис, Параметры, выбрать закладку Вид и в группе Параметры окна установить флажок Формулы.

Формат отображения числовых величин: в виде целого (16, 154) или вещественного числа (1,1755, 5,439), в показательной форме (1,45Е-4), в денежном формате (345,32) или ($345,32), в процентном формате (35%). При представлении числа в процентном формате, введенное число делится на сто. Изменение способа представления чисел, даты и времени осуществляется командой Формат, Ячейки, Число.

^ Размещение содержимого ячейки. Содержимое ячейки может быть размещено справа, слева, по центру. По умолчанию, текст прижимается к левому краю, значения - к правому краю. Текст может быть размещен горизонтально, вертикально или под определенным углом. Управление размещением содержимого ячейки осуществляется командой Формат, Ячейки, Выравнивание. Важное значение имеет флажок Переносить по словам. При установке этого флажка текст будет автоматически переноситься в пределах установленной ширины столбца. Если флажок Переносить по словам сброшен, то вводимый текст располагается в одну строку и, если соседняя ячейка заполнена, то на экране будет видна только часть текста, умещающаяся в ячейке.

Шрифт. Параметры шрифта: начертание, стиль, цвет, интервал между символами, высота шрифта и другие эффекты, относящиеся к форматированию шрифта, устанавливаются с помощью команды Формат, Ячейки, Шрифт.

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

Статус ячейки. Ячейка может иметь два статуса: защищена или не защищена. В защищенную ячейку нельзя внести информацию или изменить ее содержание. Установка режима защиты осуществляется командой Формат, Ячейки, Защита. Режим защиты ячейки вступает в силу только после защиты листа командой Сервис, Защита, Защитить лист. Для отмены защиты ячейки достаточно отменить защиту листа командой Сервис, Защита, Снять защиту лист.
^

Курсор таблицы


Курсор таблицы или контур выделения представляет собой двойную рамка, окаймляющую всю ячейку (рис. 6.1). В правом нижнем углу рамки на пересечении сторон располагается маленький квадрат - "маркер заполнения", используемый при заполнении ячеек рядом данных с постоянным шагом или при копировании. Для перемещения курсора используются клавиши управления перемещением курсора, Home - перейти в первую ячейку строки, End+клавиши управления перемещением курсора - последняя занятая ячейка в соответствующем направлении, Ctrl+Home, используются также клавиши прокрутки и ряд других комбинаций клавиш. Непосредственная адресация осуществляется вводом адреса ячейки в поле “Адрес ячейки” Строки Формул.
^

Ввод данных


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

При редактировании строки ввода используются клавиши:

    1. "", "" - сдвиг курсора строки ввода на один символ в соответствующем направлении;

    2. Insert - включение режима вставки символов;

    3. Delete - удаление символа в позиции курсора;

    4. Home, End, Tab - переход в начало или конец текста;

    5. SpaceBar (ПРОБЕЛ) - сдвиг вправо с удалением символов или без удаления символов , в зависимости от режима Вставка/Замена;

    6. BackSpace (ВОЗВРАТ НА ШАГ) - удаление символа слева от курсора;

    7. Esc - удаление вводимого текста.

Для очистки ячейки выделите ее и нажмите клавишу Delete или Пробел и Enter. Очистить ячейку можно командой Правка, Очистить. После ввода этой команды откроется дополнительное меню с запросом, что очищать: Все, Форматы, Содержимое, Примечания.

^ Признаком текста при вводе данных является апостроф ('), например: 'Сводная ведомость, 'Электронная таблица. По умолчанию вводимые данные воспринимаются как текст. Если не установлен режим переноса текста по словам, то вводимый текст заполняет все свободные ячейки справа, если соседняя справа ячейка занята, то на экране будет видна только часть текста, умещающаяся в ячейке.

Ввод даты. Дата вводится в формате ДД.ММ.ГГ или ДД.ММ.ГГГГ.

^ Ввод текстовых констант. Для ввода текстовых констант используется функция ТЕКСТ().

Тип данных в ячейке определяется при первом вводе. Для изменения формата ячейки используется команда Ячейка из меню Формат. Например, если в ячейку введена дата, то для ввода числа необходимо изменить формат ячейки командой: Формат, Ячейка. Затем выбрать закладку Число, выбрать в окне "Числовые форматы" тип "Общий" или "Числовой".

Признаком формулы является знак "=". Если при вводе формулы допущена ошибка, то программа выдает сообщение об ошибке. При вводе формулы без знака равно, программа воспринимает вводимые данные как текст. Адреса ячеек вводятся только латинскими символами.

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

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

Для ввода формул или ознакомления с функциями Excel можно использовать Мастера функций. Для этого необходимо щелкнуть инструмент f  в стандартной панели инструментов или воспользоваться командой Вставка, Функция.

Примеры записи формул:

=А2+2 - сложение; =24-12 - вычитание;

=F35/B7*$A$2 - делит значение ячейки F35 на значение ячейки B7 и умножает на значение ячейки А2. У ячейки А2 указан абсолютный адрес;

=СТАВКА*МЕСЯЦ - перемножаются значения, содержащиеся в ячейках с именами СТАВКА и МЕСЯЦ;

=ЕСЛИ(А2<B2;C3;D2*E17) - условное выражение. Если значение в ячейке А2 меньше значения в ячейке В2, то результат будет равен значению ячейки С3, иначе произведению значений ячеек D2 и Е17.
^ При записи формул, для указания адреса ячеек, значения которых не должны изменяться при копировании формул, обязательно используйте абсолютный адрес.

Для ускорения ввода признака абсолютного адреса символа $ можно воспользоваться функциональной клавишей ^ F4: установите курсор строки ввода в любом месте адреса ячейки и нажмите клавишу F4.

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

Для редактирования содержимого ячейки необходимо выделить ее, при этом содержание ячейки отображается в Строке формул. Щелкните мышкой по Строке формул и вносите необходимые изменения. Для окончания редактирования данных нажмите клавишу Enter или щелкните по кнопке  Cтроки Формул.
^

Приемы работы с электронной таблицей


  • Выбор ячейки. Установите курсор на ячейку или щелкните мышью.

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

Для выбора нескольких несвязанных областей необходимо нажать и удерживать клавишу Ctrl, а затем выделить требуемые области.

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

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

  • Изменение размеров строк и столбцов. Для изменения высоты (ширины) строки (столбца) зацепите границу строки, т.е. подведите указатель мыши к границе строки так, чтобы он изменил свою форму на двунаправленную стрелку, нажмите левую клавишу мыши и протащите указатель мыши в нужном направлении до установки требуемых размеров строки. При уменьшении размеров до нуля строки будут спрятаны. Для восстановления спрятанных столбцов, выделите столбцы слева и справа от спрятанных и введите команду Формат, Столбец, Отобразить. Чтобы восстановить спрятанные строки, выделите строки сверху и снизу от спрятанных строк и введите команду Формат, Строка, Автоподбор высоты или Формат, Строка, Отобразить.

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

  • Форматирование ячеек. Для изменения формата ячейки (группы ячеек) выделите ячейку, выберите команду Ячейки из меню  Формат. Щелкните нужную закладку и установите требуемые параметры. Для окончания работы щелкните кнопку ОК. Форматировать ячейки можно как до так и после ввода данных. С целью экономии времени применяйте формат сразу к группе ячеек после ввода данных.
^

Копирование ячеек


При копировании ячеек можно использовать команды Копировать и Вставить меню Правка, одноименные кнопки панели инструментов или мышь.

Копирование с использованием маркера автозаполнения.

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

  • выделите копируемую ячейку;

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

^ Копирование с помощью команд меню Правка

Для копирования с помощью команд меню Правка или кнопок панели инструментов необходимо выполнить следующее:

- выделить ячейку или блок ячеек, подлежащих копированию;

- выбрать команду Копировать  из меню Правка;

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

- выбрать команду Вставить из меню Правка;

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

^ Копирование с помощью мыши.

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

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

  • выделите копируемые ячейки;

  • укажите место вставки;

  • введите команду Правка, Специальная вставка и щелкните по кнопке Вставить связь.

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

Оформление таблицы


Для оформления таблицы: обрамления, заливки цветом, - можно воспользоваться закладками Граница и Вид окна диалога Формат ячеек или кнопками Границы и Цвет заливки панели инструментов Форматирование, или воспользоваться командой Автоформат меню Формат.

Для оформления таблицы с помощью команды Автоформат выполните следующее:

  • выделите таблицу и введите команду Формат, Автоформат; 

  • выберите нужный стиль оформления и щелкните по кнопке ОК.
^

Условное форматирование


Ч
асто бывает необходимо выделить какие-то результаты, чтобы обратить на них внимание: минимальные запасы сырья и материалов, предельно допустимые значения параметров среды обитания и др. В этом случае на помощь приходит Условное форматирование.


Наименование

Наличие запасных частей

Втулка

10

Поршень

15

Карбюратор

5

Тормозные накладки

10

Коробка передач в сборе

2



В
Рис. 6.4. Пример условного форматирования
ыделите ячейки, значения в которых необходимо контролировать и введите команду Формат, Условное форматирование. Открывается окно диалога Условное форматирование (рис. 6.3). В списке “Условие1” выберите “значение” или “формула”. Во втором списке выберите отношение “равно”, “не равно”, “меньше или равно” и др.. В следующем списке укажите значение, формулу, с которой должно сравниваться значение в выделенных ячейках, или ссылку на ячейку. Щелкните по кнопке Формат... . Откроется окно диалога Формат ячеек, которое позволяет настроить параметры шрифта, границы, вид (заливку ячеек цветом), узор. Пример оформления ячеек демонстрируется в окне “Отображение ячейки при выполнении условия”. Для завершения работы щелкните по кнопке ОК. Пример выполнения условного форматирования приведен на рис. 6.4.
^

Фиксация шапки таблицы


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

Предварительный просмотр


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

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

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

Закладка ^ Поля позволяет установить ширину левого и правого поля, высоту верхнего и нижнего полей и высоту поля для колонтитулов, а также центрирование текста документа на странице.

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

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

Сохранение и печать таблицы


Перед печатью целесообразно сохранить документ на диске, для этого необходимо выполнить следующее: введите команду Сохранить как...  из меню Файл, укажите в строке ввода “Имя Файла” имя файла (расширение имени файла .xls оставьте без изменения), выберите, при необходимости, из раскрывающегося списка “Папка” диск и рабочую папку, щелкните по кнопке ОК. Для вывода таблицы на печать выберите команду Печать из меню Файл, укажите, что печатать: Все, Выделенный диапазон или Номера страниц, Число копий и щелкните кнопку ОК.
^

Контрольные вопросы


1. Как изменить ширину строки (столбца)?

2. Как вставить строку, группу строк (столбцов)?

3. Каким образом устанавливается формат ячейки?

4. Что такое Строка Формул, какие поля она имеет?

5. Где можно увидеть адрес текущей ячейки?

6. Как перейти к новой ячейке?

7. Что такое абсолютный, смешанный и относительный адреса ячейки, как они записываются?

8. Как выполняется копирование ячеек?

10. Как ввести в ячейку текст, формулу?

11. Как сохранить таблицу на диске?

12. Как загрузить таблицу с диска?

13. Как вывести таблицу на печать?

14. Каким образом осуществляется фиксация шапки таблицы?

Заключение


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

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

6.2. Разработка электронных таблиц

Типы полей электронной таблицы


Таблица в Excel независимо от ее назначения, имеет четыре поля:

1 - поле описания задачи, состоящее из клеток с текстовой информацией, отражающей наименование и назначение ЭТ; глобальные параметры таблицы; описание строк и столбцов;

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

3 - поле расчетных формул, содержащее промежуточные результаты. Операндами в этих клетках являются имена клеток с числовыми данными из полей 1 и 2;

1/1

1/2

2

3

4/2




4/1




Рис. 6.7. Размещение полей электронной таблицы

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

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

Пример размещения полей приведен на рис. 6.7.

Рассмотрим пример разработки таблицы для расчета деформации балки.

Пример 6.1. Рассчитать деформацию балки закрепленной одним концом на вертикальной опоре (рис. 6.8). Тип балки I – образная, ширина 0,3 м, площадь сечения Z =2 м2, линейная, плотность - 80 кг/м, момент инерции – I = 3.68 м4, модуль упругости E=5.3 кг/м2. Нагрузка приложена на расстоянии L от закрепленного конца, величина нагрузки W. Длина балки L1.

Расчетные формулы:

Напряжение

для х<L (6.1)

Прогиб

, если x<L и , если x>=L (6.2)

Пример разработки таблицы приведен на листинге 6.1.

Порядок работы

  1. Установите курсор в ячейку А1, щелкните по строке ввода – в строке ввода появится мигающий курсор. Введите дату, например: 14.11.04 и нажмите клавишу Enter или щелкните по кнопке  в Строке формул. Измените формат представления даты командой Формат, Ячейка, откройте закладку Число, выберите в списке “Числовые форматы” слово Дата и выберите в списке “Тип” нужный формат представления даты.

  2. Введите наименование задачи в ячейку В2. Выделите ячейки B2:F2 и объедините их щелчком по соответствующей кнопке на панели инструментов Форматирование. Введите команду Формат, Ячейка на закладке Шрифт установите требуемый размер шрифта и начертание. Выравнивание текста по горизонтали выполните с помощью закладки Выравнивание или с помощью соответствующих кнопок на панели инструментов Форматирование.

  3. Внесите данные в ячейки A4:C14 согласно рисунку 6.9. Выполните обрамление блока ячеек A4:C14, используя кнопку Границы панели инструментов Форматирование или закладку Границы окна диалога Формат ячеек.

  4. Оформите аналогичным образом шапку таблицы Расчет параметров.

  5. Внесите данные в ячейки E6:E14. Для ускорения ввода данных используйте маркер автозаполнения курсора. Введите в ячейку Е6 значение 0, а в ячейку Е7 – 0,5. Выделите ячейки Е6:Е7 мышью. Зацепите мышью за маркер автозаполнения и протащите по ячейкам Е8:Е13.

  6. Внесите в ячейку F6 формулу (6.1) со ссылками на адреса ячеек:

=ЕСЛИ(E6<$C$7;$C$8/$C$12*($C$7-E6);) (6.3)

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

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

ЕСЛИ(<условие>;<выражение1>;<выражение2>)

Функция работает следующим образом: программа проверяет условие и, если оно выполняется (истинно), то возвращает результат согласно Выражению1, в ином случае возвращается результат согласно Выражению2.

Например. Требуется проверить равенство значений в ячейках A1 и B1 и вывести результат в ячейку С1.

Запишем в ячейку С1 формулу:

ЕСЛИ(А1=В1;”Выражение истинно”;”Выражение ложно”

В зависимости от значений в ячейках А1 и В1 получим следующие результаты:

Листинг 6.2. Использование функции ЕСЛИ




А

В

С

D

1

5

5

Выражение истинно










А

В

С

D

1

5

7

Выражение ложно




  1. Внесите в ячейку G6 формулу (6.2) со ссылками на адреса ячеек:

=ЕСЛИ(E6<$C$7;$C$8*E6^2/(6*$C$14*$C$13)*(3*$C$7-E6);$C$8*$C$7^2/(6*$C$14*$C$13)*(3*E6-$C$7)) (6.4)

  1. Скопируйте формулы из ячеек F6, G6 в ячейки F7:F14, используя механизм автозаполнения.
^

Функции электронной таблицы


Excel имеет 11 категорий различных функций: математические/тригонометриче­ские; инженерные; логические; текстовые; статистические; функции категории дата/время; функции для работы с базами данных/списками; финансовые; информационные и функции категории ссылки/массивы; функции проверки свойств и значений. Кроме того Excel содержит большое число надстроечных функций, которые используются для создания компьютерных программ в Excel, а также имеется возможность создания пользовательских функций и программ на Visual Basic for Applications. Можно написать программы на других языках программирования высокого уровня, например, С, FORTRAN и потом вызвать их в Excel.

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

Окно диалога имеет два списка. В левом списке приведены категории функций, а в правом – функции. В списке категорий последней в списке будет категория “Пользовательские функции”. В эту категорию попадают функции, созданные пользователем с помощью встроенного языка программирования Visual Basic for Application.

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

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

Некоторые функции приведены в табл.6.1.

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

В качестве примера использования мастера функций рассмотрим порядок ввода функции ЕСЛИ согласно выражений (6.2), (6.4):

  • Выделите ячейку G6, в которую надо поместить выражение. Введите команду Вставка функция или щелкните по одноименной кнопке fx на панели инструментов стандартная откроется окно диалога Мастер функций (рис. 6.9).

  • Найдите в правом списке функцию ЕСЛИ, выделите ее и щелкните по кнопке ОК – откроется окно диалога для ввода формул (рис. 6.10).

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

  • В
    ведите в первую строку логическое условие E6<C7.

Введите во вторую строку выражение “истина”:

$C$8*E6^2/(6*$C$14*$C$13)*(3*$C$7- E6)

Введите в третью строку выражение “ложь”

$C$8*$C$7^2/(6*$C$14 *$C$13)*(3*E6-$C$7)

Если формулы введены правильно, то сразу же можно увидеть результат. Так как условие истинно, то функция возвращает результат согласно первому выражению (строка 2).

  • Для завершения работы щелкните по кнопке ОК.

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


Таблица 6.1

^ Функции электронной таблицы




Математические

ABS()

Абсолютное значение числа

ФАКТР()

Факториал числа

ЦЕЛОЕ()

Число, округленное до ближайшего меньшего целого

ОСТАТ()

Модуль(остаток от деления двух чисел)

СЛЧИС()

Случайное число от 0 до 1

КОРЕНЬ()

Квадратный корень из числа

СУММА()

Сумма чисел в списке

СУММЕСЛИ()

Сумма значений в ячейках, соответствующих заданному критерию

СУММПРОИЗВ()

Сумма произведений элементов массивов

СУММКВ()

Сумма квадратов чисел в списке

СУММРАЗНКВ()

Сумма разностей квадратов элементов в двух массивах

СУММСУММКВ()

Сумма сумм квадратов элементов в двух массивах

СУММКВРАЗН()

Сумма квадратов разностей значений в двух массивах










Логарифмические функции

EXP()

Число е, возведенное в степень

LN()

Натуральный логарифм числа (основание “е”)

LOG()

Логарифм числа по заданному основанию LOG(число,основание)

LOG10()

Логарифм числа по основанию 10










Тригонометрические функции

ПИ()

Возвращает значение числа π

COS()

Косинус числа

SIN()

Синус числа

TAN()

Тангенс числа










Обратные тригонометрические функции

ACOS()

Арккосинус числа

ASIN()

Арксинус числа

ATAN()

Арктангенс числа от -/2 до /2

ATAN2()

Арктангенс отношения двух чисел ( от - до )










Функции преобразования угла

ГРАДУСЫ()

Показатель величины угла в градусах

РАДИАНЫ()

Показатель величины угла в радианах










Матричные функции

МОПРЕД()

Определитель матрицы

МОБР()

Матрица, обратная заданной

МУМНОЖ()

Произведение двух матриц

ТРАНСП()

Транспонирование матрицы
^

Генерирование данных


Часто бывает необходимо сгенерировать последовательность чисел, дат.

Для этой цели можно использовать механизм автозаполнения. Чтобы заполнить несколько ячеек прогрессией, необходимо записать в смежные ячейки данные, отличающиеся на величину шага, выделить эти ячейки, и перетащить маркер заполнения выделенного диапазона ячеек. Можно также воспользоваться командой Прогрессия программы Excel. Внесите в ячейку начальное значение ряда чисел; выделите область для заполнения, выберите пункт Заполнить в меню Правка, а затем щелкните пункт Прогрессия. На экран выводится диалоговое окно Прогрессия (рис. 6.11). Выберите Тип прогрессии, Расположение в соответствии с выделенной областью, Шаг и щелкните по кнопке ОК.

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

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

Для заполнения ячеек часто используемыми текстовыми записями можно создавать пользовательские прогрессии. Для этого выполните следующее: выберите команду Параметры в меню Сервис и откройте затем закладку Списки. В окне списки выберите строку ”Новый список” и введите данные в поле “Элементы списка”. После ввода каждой новой записи нажимайте клавишу Enter. Для завершения работы щелкните по кнопке Добавить.

Р

Листинг 6.3.

Табулирование функции




А

В

1

Начальное знач.

1

2

Шаг табуляции

0,5

3

Аргумент

Функция

4

=В1

=SIN(A4)

5

=A4+$B$2=

=SIN(A5)

6

=A5+$B$2

=SIN(A6)

7

=A6+$B$2

=SIN(A7)

8

=A7+$B$2

= SIN(A8)

9

=A8+$B$2

=SIN(A9)

10

=A9+$B$2

=SIN(A10)

Рис.6.13






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

  • введите в ячейку А1 текст “Начальное значение”, а в ячейку В1 начальное значение ряда;

  • введите в ячейку А2 текст “Шаг табуляции”, а в ячейку В2 значение шага табуляции (приращение аргумента);

  • запишите в ячейку А4 начальное значение ряда путем ссылки на ячейку В1: выделите ячейку В4 и запишите в нее формулу: = В1;

  • запишите в ячейку А5 формулу арифметической прогрессии A4+$B$2 ;

  • определите диапазон ячеек, куда необходимо скопировать формулу (номер начальной и конечной ячеек);

  • скопируйте формулу из ячейки А5 в остальные ячейки диапазона.
^

Табулирование функций

Табулирование функций с использованием операций

копирования


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

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

Пример табулирования функции одной переменной приведено на Листинге 6.3. Для выполнения операции табулирования необходимо:

- сгенерировать ряд значений аргумента на заданном интервале;

- записать в соседний столбец справа расчетную формулу зависимости функции от аргумента;

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

Пример табулирование функции двух переменных приведен на Листинге 6.4.


Листинг 6.4. Табулирование функции двух переменных




А

B

C

D

E

1

Шаг 1-го

аргумента

0,5

Шаг 2-го

аргумента

0,2




2




0,1

B2+$D$1

C2+$D$1

D2+$D$1

3

1

2*$A3+B$2^2

2*$A3+C$2^2

2*$A3+D$2^2

2*$A3+E$2^2

4

A3+$B$1

2*$A4+B$2^2

2*$A4+C$2^2

2*$A4+D$2^2

2*$A4+E$2^2

5

A4+$B$1

2*$A5+B$2^2

2*$A5+C$2^2

2*$A5+D$2^2

2*$A5+E$2^2

6

A5+$B$1

2*$A6+B$2^2

2*$A6+C$2^2

2*$A6+D$2^2

2*$A6+E$2^2

7

A6+$B$1

2*$A7+B$2^2

2*$A7+C$2^2

2*$A7+D$2^2

2*$A7+E$2^2

8

A7+$B$1

2*$A8+B$2^2

2*$A8+C$2^2

2*$A8+D$2^2

2*$A8+E$2^2

9

A8 +$B$1

2*$A9+B$2^2

2*$A9+C$2^2

2*$A9+D$2^2

2*$A9+E$2^2
Порядок выполнения операции следующий:

  • запишите в ячейку А3 начальное значение аргумента Х;

  • запишите в ячейку В2 начальное значение аргумента Y;

  • запишите в ячейки A4 и C2 формулы для генерирования рядов значений аргументов;

  • скопируйте в ячейки А5:А9 формулу для вычисления аргумента Х из ячейки А4;




  • скопируйте в ячейки D2:E2 формулу для вычисления Y из ячейки C2;

- запишите в ячейку В3 таблицы расчетную формулу с использованием смешанных адресов ячеек: У первого аргумента зафиксируйте столбец, а у второго аргумента - строку;

  • скопируйте формулу во все ячейки блока.

^ Общее правило при копировании формул со смешанными адресами:

Если данные находятся в строке, то фиксируется номер строки, а если данные находятся в столбце то фиксируется номер столбца.
^

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

Таблица подстановки


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

Пример 6.1. Протабулировать функцию SIN(x) на интервале от –ПИ/2 до ПИ/2 с шагом 0,5.

Решение:

  • введем в ячейки A1, А3, А5, С1 (Листинг 6.5) текст Ячейка ввода, Начальное значение, Шаг табуляции, Ячейка ввода формулы;


  • Листинг 6.5. Использование команды

    Таблица подстановки




    A

    B

    C

    1

    Ячейка ввода




    Ячейка ввода

    формулы

    2

    0




    SIN(a2)

    3

    Начальное

    значение

    -1,5708

    -1

    4

    -1,5708

    -1,0708

    -0,87758

    5

    Шаг табуляции

    -0,5708

    -0,5403

    6

    0,5

    -0,0708

    -0,07074

    7




    0,429204

    0,416147

    8




    0,929204

    0,801144

    9




    1,429204

    0,989992

    10




    1,929204

    0,936457
    введем в ячейку ввода А2 произвольное число, например 0

  • (это число не влияет на результат табулирования);

  • ведем в ячейку А4 начальное значение аргумента х - -ПИ/2. Для ввода этого числа используем функцию ПИ();

  • введем в ячейку А6 значение шага - 0,5;

  • введем в ячейку С2 формулу SIN(A2). В качестве аргумента указывается адрес ячейки ввода;

  • сгенерируем в столбце В, начиная с ячейки В3, ряд значений аргумента;

  • выделим область В2:С10 и введем команду Данные, Таблица подстановки;

  • в диалоговом окне Таблица подстановки (рис. 6.11) введем в окно ввода “Подставлять значения по строкам в...” номер Ячейки ввода А2 и щелкнем кнопку ОК. (Для ввода номера ячейки достаточно активизировать окно ввода щелчком мыши и щелкнуть по ячейке А2). Работа завершена.

Пример 6.2. Протабулировать функцию 2х+у2 при х, изменяющимся от 0 до 1 с шагом 0,2, и у изменяющимся от 1до 4 с шагом 1.

Решение:

  • обозначим ячейку А2 как ячейку ввода по строкам, а ячейку А4 как ячейку ввода по столбцам для этого внесем соответствующие записи в ячейки А1 и А3 (см. рис. 6.11);

  • внесем в столбец В, начиная с ячейки В2 значения аргумента х;

  • внесем в строку 1, начиная с ячейки С1 значения аргумента у;

  • внесем в ячейку В1 (ячейка на пересечении первого столбца и первой


  • Листинг 6.6. Табулирование функции двух

    переменных




    A

    B

    C

    D

    E

    F

    1

    По

    строкам

    2*х+у2

    1

    2

    3

    4

    2

    1

    0

    1

    4

    9

    16

    3

    По столбцам

    0,2

    1,4

    4,4

    9,4

    16,4

    4

    1

    0,4

    1,8

    4,8

    9,8

    16,8

    5




    0,6

    2,2

    5,2

    10,2

    17,2

    6




    0,8

    2,6

    5,6

    10,6

    17,6

    7




    1

    3

    6

    11

    18
      1   2   3   4   5   6   7   8



    Скачать файл (393.1 kb.)

    Поиск по сайту:  

© gendocs.ru
При копировании укажите ссылку.
обратиться к администрации