Excel — это программа, предназначенная для создания таблиц, выполнения вычислений и анализа данных. Такие программы называются редакторами электронных таблиц. В приложении Excel можно создавать таблицы, в которых автоматически будут подсчитываться итоговые значения для числовых данных, печатать аккуратно оформленные таблицы и строить простые графики.
Приложение Excel входит в состав Office, пакета программных продуктов, в котором объединены программы нескольких типов, предназначенные для создания документов, электронных таблиц и презентаций, а также для работы с электронной почтой.
Компоненты рабочего листа.
Основу структуры листа Microsoft Excel составляют столбцы, которые располагаются сверху вниз, и, строки, проходящие слева направо. Столбцы обозначаются буквами, а строки - цифрами. Заголовки столбцов - это буквы A, B, C..., а заголовки строк - это цифры 1, 2, 3 и т. д. На пересечении каждого столбца и каждой строки располагается небольшой прямоугольник, называемый ячейка.
Каждая ячейка имеет свой адрес, который определяется соответствующими столбцом и строкой. Например, на пересечении столбца С со строкой 3 располагается ячейка С3. Адрес ячейки называют еще ссылкой на ячейку. Если щелкнуть на ячейке, поместив в нее курсор, то она становится активной и автоматически выделяется жирной рамкой. В ее пределах осуществляется ввод или редактирование данных или формул. Координаты активной ячейки отображаются в поле имени. Конкретные данные или формулы вводятся прямо в ячейку (для этого достаточно дважды кликнуть на нужной ячейке или выделить ячейку и нажать клавишу F2) или в строке формул.
Диапазоны и ссылки.
В ячейке размещаются числа, текст и формулы. Можно работать с одной ячейкой, а можно выделить несколько ячеек и работать с ними одновременно. Совокупность нескольких выделенных ячеек называется диапазоном. Вместо ввода строки "ячейки с А1 по А9" используют сокращенную запись А1:А9. Ссылка на диапазон складывается из разделенных двоеточием адресов первой и последней ячеек этого диапазона.
Выбор диапазона.
Существует множество способов выбора диапазона. Если щелкнуть на заголовке столбца, то в качестве диапазона будет выделен весь этот столбец. Выделение строк осуществляется аналогично.
Если выделить ячейку B2 и, удерживая нажатой кнопку мыши, перетащить указатель мыши на ячейку F2, будет выделен диапазон B2:F2.
Можно выделить весь рабочий лист, щелкнув на кнопке Select All (Выделить все). Она не маркирована, но отыскать ее можно в верхнем левом углу на пересечении заголовков столбцов и строк.
Выделение несмежных ячеек.
Иногда приходится работать одновременно с данными, которые расположены в несмежных ячейках. Выделение несмежных диапазонов в Excel реализуется очень просто:
Выделите протаскиванием указателя часть искомого диапазона, состоящую из смежных ячеек.
Переведите указатель мыши в начало другого фрагмента смежных ячеек.
Удерживая нажатой клавишу Ctrl, щелкните на первой ячейке этого фрагмента, после чего выделите протаскиванием указателя весь фрагмент.
Воспользуйтесь сочетанием Ctrl+протаскивание указателя применительно к другим фрагментам диапазона (если таковые еще имеются).
Работа с окном Microsoft Excel
Закрепление шапки таблицы.
Если Вы перемещаетесь по рабочему листу вниз или вправо, из поля зрения могут исчезнуть строки и/или столбцы, которые необходимо видеть во время ввода табличных данных (шапка таблицы). Для решения рассматриваемой проблемы предусмотрена возможность фиксации части окна на одном месте, в то время как оставшаяся часть окна будет свободно прокручиваться.
Чтобы зафиксировать часть окна Excel, следует:
1. Выделить ячейку, расположенную ниже и правее тех строк и столбцов, которые предполагается зафиксировать.
2. В меню Window (Окно) выбрать команду Freeze panes (Закрепить область).
Для отмены закрепления следует в меню Window (Окно) выбрать команду Unfreeze panes (Снять закрепление областей).
Разделение окна.
Закрепление - это очень удобно, но иногда возникает необходимость независимой прокрутки двух или более фрагментов одного рабочего листа. Чтобы добиться этого, следует разделить окно Microsoft Excel. Для этого протаскивают до нужной позиции указатель разделения (вертикальный или горизонтальный).
Расположение окон на экране.
Зачастую для восприятия достаточно экрана, заполненного информацией из одной таблицы. Но бывают случаи, когда необходимо видеть на экране несколько листов или даже несколько рабочих книг одновременно. Для этого:
1. В меню Window (Окно) выберите команду New (Новое). На экране появляется новое окно листа.
2. Щелкните в новом окне на ярлычке другого листа, чтобы на экране отображались два разных рабочих листа одновременно.
3. В меню Window (Окно) выберите команду Arrange (Расположить), после чего установите режим расположения: Tiled (Рядом), Horizontal (Сверху вниз), Vertical (Справа налево), Cascade (Каскадом).
Настройка параметров рабочей книги.
- Выбрав вкладку General (Общие) диалогового окна Options (Параметры), Вы можете:
- Изменить стиль ссылок с A1 на R1C1. В этом случае нумеруются как строки, так и - столбцы. Адрес ячейки C2, например, будет выглядеть R2C3.
- Включить проверку открываемых книг на наличие макросов, которые могут содержать вирусы. Будет появляться сообщение в том случае, если книга содержит макросы, пользовательские панели инструментов, меню или значки.
- Включить звуковое сопровождение таких событий, как сохранение, открытие, печать, вывод сообщения об ошибках.
- Задать количество файлов, отображаемых в меню File (Файл).
- Определить количество листов, включаемых в новую рабочую книгу при создании.
- Задать стандартный шрифт и его размер. Стандартный шрифт по умолчанию предлагается для ввода данных в ячейки, кроме этого ширина столбцов задается в количествах символов стандартного шрифта.
- Указать каталог, который будет предлагаться по умолчанию для сохранения и открытия документов (рабочий каталог).
- Задать путь к папке автозагрузки, в которой находятся ссылки на книги, загружаемые автоматически при запуске Excel.
Настройка окна Microsoft Excel
На вкладке View (Вид) диалогового окна Options (Параметры) Вы можете указать элементы окна, которые следует отобразить на экране (строка формул, строка состояния, примечания, объекты, сетка, заголовки строк и столбцов, символы структуры, полосы прокрутки, ярлыки листов, разделители страниц). Кроме этого:
Включив флажок Formulas (формулы), Вы отобразите формулы прямо в ячейках.
Включение флажка Zero values (нулевые значения), приведет к выводу 0 в ячейках, содержащих нулевые значения (в противном случае эти ячейки останутся пустыми).
Вы можете выбрать цвет сетки. Если выбран параметр Auto (Авто), цвет сетки будет соответствовать цвету, установленному на панели управления Windows.
Настройка панелей инструментов и меню.
1. Из меню View (Вид) выберите команду Toolbars (Панели инструментов).
2. В появившемся каскадном меню щелкните на слове Chart (Диаграмма).
3. После появления на экране панели инструментов Диаграмма попробуйте поменять ее размер, перетаскивая ее границы.
4. Теперь перетащите панель к верхней кромке экрана. Происходит фиксация панели.
5. Чтобы снова убрать эту панель инструментов, установите указатель мыши на любом фоновом фрагменте панели и, удерживая кнопку мыши, перетащите панель на рабочий лист, а затем щелкните на кнопке Х на панели. Или вновь войдите в меню View (Вид), выберите команду Toolbars (Панели инструментов) и снова щелкните на слове Chart (Диаграмма), отключая отображение панели.
Чтобы сформировать панель инструментов по своему усмотрению:
1. Выберите команду Toolbars (Панели инструментов) меню View (Вид).
2. В появившемся каскадном меню щелкните на слове Customize (Настройка).
3. Если панель инструментов отсутствует на экране, выберите ее на вкладке Toolbars (Панели инструментов). Если нужной панели инструментов нет в списке, создайте ее, щелкнув на клавише New (Создать).
4. На вкладке Commands (Команды) выделите соответствующую категорию и перетащите нужную команду на панель инструментов с помощью мышки.
При открытом диалоговом окне Customize (Настройка) кнопки на панелях инструментов доступны для изменений (Вы можете поменять их размер; перетащить на другое место; удалить, утащив за пределы панели инструментов). Кликнув на кнопке правой клавишей мышки, Вы сможете назначить макрос, который она будет выполнять.
Аналогично производится создание и настройка меню. Для создания нового меню Вы должны воспользоваться вкладкой Commands (Команды) диалогового окна Customize (Настройка). Выберите в списке категорий New menu (Новое меню) и перетащите его в подходящее место с помощью мыши. Чтобы добавить команду к этому меню, выделите в списке соответствующую категорию и перетащите нужную команду в новое место с помощью мыши. Необходимо удерживать кнопку мыши в нажатом состоянии, пока на экране не появится пустой список команд нового меню, и поместить команду в этот список.
Чтобы восстановить встроенное меню или исходный вид команды меню (кнопки на панели инструментов):
1. Откройте диалоговое окно Customize (Настройка).
2. Кликните правой клавишей мышки на нужном меню, команде или кнопке на панели инструментов.
3. Выберите команду Reset (Сброс) в открывшемся контекстном меню.
Чтобы восстановить встроенную панель инструментов:
1. Откройте диалоговое окно Customize (Настройка).
2. Выберите панель инструментов на вкладке Toolbars (Панели инструментов).
3. Выберите команду Reset (Сброс).
Использование мастера функций.
Не все функции в Excel определяются так же просто, как функция SUM (СУММ). Для некоторых из них нужно много аргументов, к тому же аргументы должны быть правильных типов и введены синтаксически правильно. Чем сложнее функция, тем сложнее правильно ее использовать. Мастер функций существенно облегчает запись функций.
Щелкните на кнопке Paste Function (Вставка функции), и мастер:
отобразит список функций с их описаниями;
- подскажет количество и типы аргументов;
- предложит компактное описание каждой функции и ее аргументов;
- поможет отредактировать уже имеющиеся на рабочем листе функции;
- сформирует функцию с необходимыми аргументами, а затем поместит в выделенную ячейку.
Изначально Excel в списке Function category (Категория) отображает стандартный список категорий функций. После того, как Вы поработаете с мастером функций, последний включит в список категорий перечень функций, использовавшихся последними («10 недавно использовавшихся»).
Рассмотрим работу мастера функций более подробно на примере вычисления серединного значения. Предположим, что Вам понадобилось найти медиану для диапазона значений (медиана - это середина множества чисел, т. е. половина чисел меньше этого значения, половина - больше). В этом случае придется обратиться к мастеру функций и использовать функцию MEDIAN (МЕДИАНА):
Выделите ячейку для помещения результата.
Щелкните на кнопке Paste Function (Вставка функции) и выберите из списка Function category (Категория) пункт "Statistical" ("Статистические"); затем прокрутите список имен функция найдите и выберите функцию MEDIAN (МЕДИАНА).
Щелкните на кнопке ОК, после чего на экране появится диалоговое окно.
Щелкните на поле "Number1" ("число 1"), чтобы поместить туда курсор (если он там отсутствует). С помощью мыши выделите диапазон значений для функции ( в рассматриваемом примере это A6:C6). Можно также ввести каждое значение отдельно в полях Number1 (число 1), Number2 (число 2) и т. д. (Excel добавит при необходимости еще поля), но удобнее и быстрее выделить диапазон.
Результат появится в поле Formula result (Значение). Теперь щелкните на кнопке ОК. Медиана будет перенесена в выделенную ячейку.
Функция SUM (СУММ) используется очень часто, а поэтому она выведена на стандартную панель инструментов (кнопка AutoSum (Автосумма)). Для вызова любых других функций следует щелкнуть на кнопке Paste Function (Вставка функции). Рассмотрим более подробно некоторые наиболее часто используемые функции:
Логические функции.
Функцию IF (ЕСЛИ) удобно использовать для проверки содержимого ячейки. Функция возвращает одно значение, если указанное значение истинно и другое, если оно ложно.
Статистические функции.
Функция COUNT (СЧЕТ) в Excel вычисляет количество чисел в списке аргументов. Эта функция считает также даты и время, т. к. внутреннее представление таких данных - численное. Эта функция игнорирует текст, логические значения или пустые ячейки, так как их нельзя конвертировать в числа.
Чтобы сосчитать не только числа, но и текстовые значения (например, количество клиентов в таблице), предусмотрена функция COUNTA (СЧЕТЗ). Эта функция считает количество значений в списке аргументов и игнорирует только пустые ячейки.
Функции даты и времени.
Всякий раз, когда Вы вводите дату, Excel преобразует введенное значение в порядковый номер. В связи с таким представлением дат календарь Excel имеет свои особенности: он начинается 1 января 1900 года и заканчивается 31 декабря 9999 года (это, так называемая, система дат 1900). При вводе даты в ячейку рабочего листа Excel она выглядит как нормальная календарная дата. Но во внутреннем представлении Excel даты — это порядковые номера от 1 до 2 потому что с 1 января 1900 года по 31 декабря 9999 года 2958325 дней. Таким образом, даты в интервале с 01.01.1900 по 31.12.9999 хранятся в Excel как соответствующий порядковый номер. Например, 31.05.57 хранится как 20971, т. к. между 01.01.1900 и 31.05.57 как раз 20971 день. Такое представление дат в Excel позволяет оперировать с ними как с числами. Система дат 1900 по умолчанию используется в Microsoft Excel для Windows.
Надо заметить, что в Microsoft Excel для Macintosh по умолчанию используется система дат 1904, когда за точку отсчета берется 2 января 1904 года. MS Excel для Windows позволяет выбрать эту систему дат, для этого достаточно установить флажок 1904 date system (система дат 1904) на вкладке Calculation (Вычисления) диалогового окна Options (Параметры) меню Tools (Сервис). Если Вы открываете документ, подготовленный для другой аппаратной платформы, смена системы дат произойдет автоматически.
Функция DATE (ДАТА) запрашивает ввод года, месяца и дня. Затем она возвращает соответствующую дату. То, в каком виде отображается дата в ячейке рабочего листа, зависит от режима форматирования этой ячейки. Но вне зависимости от внешнего представления Excel хранит дату в виде порядкового номера.
Операторы.
В Excel предусмотрены следующие операторы:
Клавиша Опреатор Выражение Результат
+ сложение =5+3 8
- вычитание =6-4 2
* умножение =8*4 32
/ деление =9/3 3
^ возведение в степени =4^2 16
% процент =60% 0,6
Знак равенства в формулах Excel.
Вне зависимости от того, создаете ли вы в Excel самую простую или очень сложную формулу, один элемент всегда постоянен: формулы всегда начинаются со знака равенства "=". Например, чтобы заставить Excel вычислить, сколько будет 2*2, следует записать =2*2.
Поиск непарной скобки в формуле.
Если в формуле имеется множество пар скобок, существует опасность пропустить одну из них. Чтобы этого не произошло, в Excel предусмотрена возможность определения пропущенной скобки: по мере перемещения текстового курсора по формуле отдельные пары скобок кратковременно выделяются жирным шрифтом, например:
Непарная скобка жирным шрифтом не выделяется. Если Вы все-таки попытаетесь зафиксировать эту формулу, нажав клавишу Enter, появится диалоговое окно, сообщающее об ошибке и предлагающее исправить ее. Вы не сможете работать дальше до тех пор, пока не справитесь с этой проблемой.
Вложенные скобки в формуле выделяются разными цветами (каждая пара скобок имеет свой цвет).
Операторы сравнения.
Операторы сравнения — это:
Клавиша Опреатор Выражение Результат
= равно =5=3 ЛОЖЬ
> больше =6>4 ИСТИНА
< меньше =8<4 ЛОЖЬ
>= не меньше =9>=9 ИСТИНА
<= не больше =4<=2 ЛОЖЬ
<> не равно =6<>0 ИСТИНА
Результат выполнения этих операторов в формуле может принимать значение TRUE (ИСТИНА) или FALSE (ЛОЖЬ). Это так называемые логические значения. Для чего они нужны? Предположим, что Вы создали рабочий лист, в котором проверяются взносы клиентов. Вы решаете, что если суммарный взнос клиента меньше 800000 руб., то не стоит его беспокоить. В противном же случае необходимо выписать счет. В один столбец можно поместить имя клиента, в следующий — сумму его предыдущих взносов, а далее — последний платеж и новый баланс. В последнем столбце помещаем формулу сравнения итогового баланса с суммой в 800000 руб. Если эта сумма выплачена, то результатом сравнения является ИСТИНА, а клиенту автоматически направляется новый счет.
Текстовый оператор.
Амперсант (&) служит для объединения строковых значений. Так, результатом выполнения выражения "Информационные " & "системы" будет строковое значение "Информационные системы"
Операторы ссылок (адресные операторы).
Операторы ссылок используют в формулах для определения диапазонов, участвующих в вычислениях.
Двоеточие (:), используемое для определения диапазона, называется оператором диапазона. Например, запись AI:D4 определяет диапазон, включающий все ячейки от А1 до D4. Если Вы хотите включить все ячейки в строке или столбце в свою формулу, то укажите, например, Е:Е для всего столбца Е или 3:8 для всех ячеек в строках с 3 по 8.
Точка с запятой (;) — оператор объединения. Он объединяет не менее двух ссылок на несмежные ячейки или диапазоны. AI;D4 означает "ячейка А1 и ячейка D4". AI:D4;FI:H4 означает "диапазон AI:D4 и диапазон FI:H4".
Пробел - оператор пересечения, который ссылается на общие ячейки диапазонов. Например, результатом выражения B5:B15 A7:D7 будет ссылка на ячейку B7, поскольку она является общей для этих двух диапазонов.
Ошибки в формулах Microsoft Excel.
Если формула обработана неправильно, Microsoft Excel отображает ошибку. Причины возникновения ошибок могут быть самыми разными:
- ##### - результат обработки формулы не умещается в ячейке или результатом - выполнения формулы, оперирующей датами и временем, является отрицательное число.
- #ЗНАЧ! - используется недопустимый тип аргумента или операнда.
- #ДЕЛ/0! - в формуле предпринимается попытка деления на ноль.
- #ИМЯ? - Excel не может распознать имя, используемое в формуле.
- #Н/Д - неопределенные данные (чаще всего встречается, если некорректно определены аргументы функции).
- #ССЫЛКА! - используется недопустимая ссылка на ячейку (например, ячейки, на которые ссылается формула, были удалены).
- #ЧИСЛО! - возвращаемое числовое значение слишком велико или слишком мало, чтобы его можно было представить в Microsoft Excel (диапазон отображаемых чисел от -10307 до 10307).
- #ПУСТО! - задано пересечение двух областей, которые в действительности не имеют общих ячеек.
Ошибки могут возникать не только из-за неправильной обработки формулы, ошибка может содержаться в ячейке, на которую ссылается формула.
Поиск параметра.
Специальная функция Goal Seek (Подбор параметра) позволяет определить параметр (аргумент) функции если известно ее значение. При подборе параметра значение влияющей ячейки (параметра) изменяется до тех пор, пока формула, зависящая от этой ячейки, не возвратит заданное значение.
Рассмотрим процедуру поиска параметра на простом примере: решим уравнение 10 * x - 10 / x = 15. Здесь параметр (аргумент) - x. Пусть это будет ячейка A3. Введем в эту ячейку любое число, лежащее в области определения функции (в нашем примере это число не может быть равно нулю). Это значение будет использовано в качестве начального. Пусть это будет 3. Введем формулу =10*A3-10/A3, по которой должно быть получено требуемое значение, в какую-либо ячейку, например, B3. Теперь можно запустить функцию поиска параметра, выбрав команду Goal Seek (Подбор параметра) в меню Tools (Сервис). Введите параметры поиска:
- В поле Set cell (Установить в ячейке) введите ссылку на ячейку, содержащую необходимую формулу.
- Введите искомый результат в поле To value (Значение).
- В поле By changing cell (Изменяя значение ячейки) введите ссылку на ячейку, содержащую подбираемое значение.
По окончании работы функции на экране появится окно, в котором будут отображены результаты поиска. Найденный параметр появится в ячейке, которая была для него зарезервирована. Обратите внимание на тот факт, что в нашем примере уравнение имеет два решения, а параметр подобран только один - это происходит потому, что параметр изменяется только до тех пор, пока требуемое значение не будет возвращено. Первый найденный таким образом аргумент и возвращается к нам в качестве результата поиска. Если в качестве начального значения в нашем примере указать -3, тогда будет найдено второе решение уравнения: -0,5.
Поиcк решения.
Надстройка Microsoft Excel Solver (Поиск решения) не устанавливается автоматически при обычной установке:
В меню Tools (Сервис) выберите команду Add-Ins (Надстройки). Если диалоговое окно Add-Ins (Надстройки) не содержит команды Solver (Поиск решения), нажмите кнопку Browse (Обзор) и укажите диск и папку, в которой содержится файл надстройки Solver. xla (как правило, это папка Library\Solver) или запустите программу установки Microsoft Office, если найти файл не удается.
В диалоговом окне Add-Ins (Надстройки) установите флажок Solver (Поиск решения).
Процедура поиска решения позволяет найти оптимальное значение формулы, содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, связанных с формулой в целевой ячейке. Процедура изменяет значения во влияющих ячейках до тех пор, пока не получит оптимальный результат по формуле, содержащейся в целевой ячейке. Чтобы сузить множество значений, применяются ограничения, которые могут иметь ссылки на другие влияющие ячейки. Процедуру поиска решения можно также использовать для определения значения влияющей ячейки, которое соответствует экстремуму целевой ячейки, например, количество учебных занятий, обеспечивающее максимальную успеваемость.
В диалоговом окне Solver (Поиск решения) так же, как и в диалоговом окне Goal Seek (Подбор параметра), необходимо указать целевую ячейку, ее значение и ячейки, которые следует изменять для достижения цели. Для решения задач оптимизации целевую ячейку следует указать равной максимальному или минимальному значению.
Если Вы щелкните на кнопке Guess (Предположить), Excel сам попытается найти все ячейки, влиящие на формулу.
Вы можете добавить граничные условия, кликнув на клавише Add (Добавить).
Кликнув на кнопке Options (Параметры), можно изменить условия поиска решения: максимальное время поиска решения, количество итераций, точность решения, допуск на отклонение от оптимального решения, метод экстраполяции (линейная или квадратичная), алгоритм оптимизации и т. д.
Вернемся к предыдущему примеру: для того, чтобы получить второе (неположительное) решение, достаточно добавить граничное условие A3<=-0.01. Так же как и при подборе параметра, на экране появится окно, в котором будет отображен отчет о результатах поиска требуемого решения. Само решение будет показано в предназначенных для него ячейках (в ячейке A3 отобразится значение -0.50).
Надстройка Microsoft Excel Solver (Поиск решения) позволяет, также, решать системы уравнений или неравенств. Рассмотрим простой пример: попробуем решить систему уравнений
x + y = 2
x - y = 0
Введем в ячейки, предназначенные для решения (A1:A2) произвольные величины, лежащие в области определения (начальные значения).
В ячейки B1 и B2 внесем формулы, по которым должны вычисляться правые части уравнений (= A1 + A2 и = A1 - A2).
Запустим Solver (Поиск решения) из меню Tools (Сервис).
Выберем одну из ячеек, содержащих формулы, в качестве целевой ячейки (например, B1), сделаем ее равной 2.
Кликнем на кнопке Guess (Предположить) для того, чтобы Excel определил влияющие ячейки (A1:A2).
Добавим ограничение B2 = 0.
Кликнем на клавише Solve (Выполнить).
Результаты поиска отобразятся в предназначенных для решения ячейках (A1:A2), отчет о результатах появится на экране.
Поиск файлов
Способов найти файл, с которым Вы работали недавно несколько. Первое это Excel сам хранит список файлов, с которыми Вы работали. Вот здесь.
Сколько будет хранится в этом списке файлов устанавливается в меню "Сервис - Параметры", вкладка "общие".
Еще один список документов, с которыми вы работали храниться в Пуск - Документы.
Ну а если в списке его нет, то можно найти через поиск.
| |
| |
|
