Скачать

Особенности работы с Microsoft Excel и Windows

Расчётные операции в Excel

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

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

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

Электронные таблицы Microsoft Excel очень мощное средство создания и ведения различных электронных документов.

После запуска программы экран Excel содержит пять областей (по порядку сверху вниз):

строка меню;

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

строка формул;

окно книги;

строка состояния.

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

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

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

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

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

Ниже панели инструментов расположена строка формул.

Содержимое активной (выделенной в данный момент) ячейки Excel всегда появляется в строке формул. В процессе ввода или редактирования данных в ячейке, в строке формул появляются три кнопки:

- кнопка отмены (красный крестик);

- кнопка ввода (зеленая галочка);

- кнопка изменения формулы (знак функции).

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

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

Существует два типа данных, которые можно вводить в ячейки листа Excel - константы и формулы.

Константы в свою очередь подразделяются на: числовые значения, текстовые значения, значения даты и времени, логические значения и ошибочные значения.

Числовые значения могут содержать цифры от 0 до 9, а также спецсимволы + - Е е ( ) . , $ % /

Для ввода числового значения в ячейку необходимо выделить нужную ячейку и ввести с клавиатуры необходимую комбинацию цифр. Вводимые цифры отображаются как в ячейке, так и в строке формул. По завершению ввода необходимо нажать клавишу Enter. После этого число будет записано в ячейку. По умолчанию после нажатия Enter активной становится ячейка, расположенная на строку ниже, но командой "Сервис"-"Параметры" можно на вкладке "Правка" установить необходимое направление перехода к следующей ячейке после ввода, либо вообще исключить переход. Если после ввода числа нажать какую-либо из клавиш перемещения по ячейкам (Tab, Shift+Tab…), то число будет зафиксировано в ячейке, а фокус ввода перейдет на соседнюю ячейку.

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

1. Если надо ввести отрицательное число, то перед числом необходимо поставить знак "-" (минус).

2. Символ Е или е используется для представления числа в экспоненциальном виде. Например, 5е3 означает 5*1000, т.е. 5000.

3. Числа, заключенные в скобки интерпретируются как отрицательные, даже если перед числом нет знака минуса. Т.е. (40) - для Excel означает -40.

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

5. Для ввода денежного формата используется знак доллара ($).

6. Для ввода процентного формата используется знак процента (%).

7. Для ввода даты и дробных значений используется знак косой черты (/). Если Excel может интерпретировать значение как дату, например 1/01, то в ячейке будет представлена дата - 1 января. Если надо представить подобное число как дробь, то надо перед дробью ввести ноль - 0 1/01. Дробью также будет представлено число, которое не может быть интерпретировано как дата, например 88/32.

Если возникает необходимость ввода числа как текстового значения, то перед числом надо поставить знак апострофа, либо заключить число в кавычки - '123 "123".

Различить какое значение (числовое или текстовое) введено в ячейку можно по признаку выравнивания. По умолчанию текст выравнивается по левому краю, в то время как числа - по правому.

При вводе значений в диапазон ячеек ввод будет происходить слева-направо и сверху-вниз. Т.е. вводя значения и завершая ввод нажатием Enter, курсор будет переходить к соседней ячейке, находящейся справа, а по достижении конца блока ячеек в строке, перейдет на строку ниже в крайнюю левую ячейку.

Для изменения значений в ячейке до фиксации ввода надо пользоваться, как и в любом текстовом редакторе, клавишами Del и Backspace. Если надо изменить уже зафиксированную ячейку, то надо дважды щелкнуть на нужной ячейке, при этом в ячейке появится курсор. После этого можно производить редактирование данных в ячейке. Можно просто выделить нужную ячейку, а затем установить курсор в строке формул, где отображается содержимое ячейки и затем отредактировать данные. После окончания редакции надо нажать Enter для фиксации изменений. В случае ошибочного редактирования ситуацию можно "отмотать" назад при помощи кнопки "Отменить" (Ctrl+Z).

Для защиты отдельных ячеек надо воспользоваться командой "Сервис"-"Защита"-"Защитить лист". После включения защиты изменить заблокированную ячейку невозможно. Однако, не всегда необходимо блокировать все ячейки листа. Прежде чем защищать лист, выделите ячейки, которые надо оставить незаблокированными, а затем в меню "Формат" выберите команду "Ячейки". В открывшемся окне диалога "Формат ячеек" на вкладке "Защита" снимите флажок "Защищаемая ячейка". Следует иметь ввиду, что Excel не обеспечивает индикации режима защиты для отдельных ячеек. Если необходимо отличать заблокированные ячейки, можно выделить их цветом. В защищенном листе можно свободно перемещаться по незаблокированным ячейкам при помощи клавиши Tab.

Теперь переходим к самому интересному - созданию формул. Собственно это то, ради чего и разрабатывались электронные таблицы.

Порядок ввода формулы

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

Выделим произвольную ячейку, например А1. В строке формул введем =2+3 и нажмем Enter. В ячейке появится результат (5). А в строке формул останется сама формула.

Рисунок 1

Поэкспериментируйте с различными арифметическими операторами: сложения (+), вычитания (-), умножения (*), деления (/). Чтобы их правильно использовать надо четко представлять их приоритет.

1. В первую очередь выполняются выражения внутри скобок.

2. Умножение и деление имеют более высокий приоритет чем сложение и вычитание.

3. Операторы с одинаковым приоритетом выполняются слева направо.

Мой вам совет - ИСПОЛЬЗУЙТЕ СКОБКИ. В этом случае вы обезопасите себя от случайной ошибки в вычислениях с одной стороны, а с другой - скобки значительно облегчают чтение и анализ формул. Если в формуле количество закрывающих и открывающих скобок не совпадает, Excel выдаст сообщение об ошибке и предложит вариант ее исправления. Сразу после ввода закрывающей скобки Excel отображает жирным шрифтом (или другим цветом) последнюю пару скобок, что очень удобно при наличии в формуле большого количества скобок.

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

Введите в ячейку А1 число 10, а в ячейку А2 - число 15. В ячейке А3 введите формулу =А1+А2. В ячейке А3 появится сумма ячеек А1 и А2 - 25. Поменяйте значения ячеек А1 и А2 (но не А3!). После смены значений в ячейках А1 и А2 автоматически пересчитывается значение ячейки А3 (согласно формулы).

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

Выделить ячейку А3 и ввести в строке формул знак равенства.

Щелкнуть на ячейке А1 и ввести знак плюс.

Щелкнуть на ячейке А2 и нажать Enter.

Результат будет аналогичным.

Относительные, абсолютные и смешанные ссылки

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

Абсолютная ссылка указывает на ячейку, местоположение которой неизменно. Обозначение абсолютной ячейки - $A$1.

Смешанная ссылка содержит комбинацию относительной и абсолютной ссылок - $A1, A$1.

Для быстрого изменения типа ссылки используется клавиша F4. Введите в ячейку А1 любое число. В ячейку А2 введите формулу =А1. Затем нажимайте клавишу F4. После каждого нажатия клавиши тип ссылки будет меняться.

Рисунок 2

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

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

А1 - 20 В1 - 200

А2 - 30 В2 - 300

В ячейку А3 введите формулу =А1+А2 и нажмите ввод.

Теперь установите курсор на правый нижний угол ячейки А3, нажмите правую кнопку мыши и протяните на ячейку В3 и отпустите кнопку мыши. Появится контекстное меню, в котором надо выбрать "Копировать ячейки".

После этого значение формулы из ячейки А3 будет скопировано в ячейку В3. Активизируйте ячейку В3 и посмотрите какая формула получилась - В1+В2. Почему так получилось? Когда мы записали в ячейке А3 формулу А1+А2, Excel интерпретировал эту запись так: "Взять значения из ячейки, расположенной в текущем столбце на две строки выше и сложить со значением ячейки, расположенной в текущем столбце на одну строку выше". Т.е. скопировав формулу из ячейки А3, например, в ячейку С43, мы получим - С41+С42. Вот в этом прелесть относительных ссылок, формула как бы сама подстраивается под наши задачи.

Рассмотрим теперь абсолютные ссылки.

Введите в ячейки следующие значения:

А1 - 20 В1 - 200

А2 - 30 В2 - 300

Введите в ячейку С1 число 5.

В ячейку А3 введите следующую формулу =А1+А2+$C$1. Аналогичным образом скопируйте формулу из А3 в В3. Посмотрите что получилось. Относительные ссылки "подстроились" под новые значения, а вот абсолютная - осталась неизменной.

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

Например, чтобы записать в ячейку А1 (Лист 1) ссылку на ячейку А5 (Лист2), надо проделать следующее:

- выделить ячейку А1 и ввести знак равенства;

- щелкните на ярлыке "Лист 2";

- щелкните на ячейке А5 и нажмите клавишу ввода;

- после этого будет активизирован опять Лист 1 и в ячейке А1 появится следующая формула =Лист2!А5.

Ссылка на ячейку из другой книги будет выглядеть так - =(Книга2)Лист2!А5.

Редактирование формул происходит аналогично редактированию текстовых значений в ячейках. Т.е. надо активизировать ячейку с формулой выделением или двойным щелчком мыши, а затем произвести редактирование, используя, при необходимости, клавиши Del, Backspace. Фиксация изменений выполняется клавишей Enter.

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

С текстовыми значениями можно выполнять математические операции, если текстовые значения содержат только следующие символы:

Цифры от 0 до 9 , + - е Е /

Еще можно использовать пять символов числового форматирования:

$ % ( ) пробел

При этом текст должен быть заключен в двойные кавычки.

Неправильно: =$55+$33

Правильно: ="$55"+$"33"

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

Для объединения текстовых значений служит текстовый оператор & (амперсанд). Например, если ячейка А1 содержит текстовое значение "Юрий", а ячейка А2 - "Кордык", то введя в ячейку А3 следующую формулу =А1&А2, получим "ЮрийКордык". Для вставки пробела между именем и фамилией надо написать так =А1&" "&А2.

Амперсанд можно использовать для объединения ячеек с разными типами данных. Так, если в ячейке А1 находится число 10, а в ячейке А2 - текст "мешков", то в результате действия формулы =А1&А2, мы получим "10мешков". Причем результатом такого объединения будет текстовое значение.

Пусть дана таблица, содержащая накладную на товар, имеющийся на некотором складе:

Рисунок 13

Здесь данные, которые содержатся в колонках Наименование товара, Цена (руб. / кг.), Количество (кг.)- основные, а данные в колонке Сумма- производные, т.к. вычисляются по формуле:

Сумма = Цена * Количество

Формула это некоторое математическое выражение, совокупность операций и операндов, так же как и в математике.

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

Значение это числовая константа, вычисляемая по формуле.

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

Рассмотрим таблицу из примера и ее реализацию в Excel.

Рисунок 14