Самоучитель функции excel с примерами. Формулы EXCEL с примерами — Инструкция по применению

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ

ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ

РОСТОВСКИЙ ГОСУДАРСТВЕННЫЙ СТРОИТЕЛЬНЫЙ УНИВЕРСИТЕТ

Утверждено на заседании кафедры ИСС

Методические указания

и контрольные задания по дисциплине «Информатика» Части 1 и 2

для бакалавров заочной формы обучения

Ростов-на-Дону

Расширенные возможности Microsoft Excel 2007. Решение практических задач.

Аннотация

Данное учебное пособие описывает расширенные возможности Microsoft Office Excel 2007, программы входящей а состав программного пакета Microsoft Office 2007 (русская версия)

Общие сведения

Размеры листа

В ранних версиях Excel на каждом листе 65 536 строк и 256 столбцов.

В Excel 2007 больше доступного пространства – лист содержит:

Увеличить размер листа книги, созданной в формате Excel 97–2003:

1. открыть файл в программе Excel 2007,

2. выбрать Офис – Преобразовать , сохранить в формате Excel 2007.

При сохранении больших таблиц (более 65 536 строк) в формате ранних выпусков Excel важно помнить, что данные, превосходящие размеры листа в 65 536 строк и 265 столбцов будут потеряны.

Чтобы использовать Excel 2000–2003 для открытия и сохранения файлов, созданных в формате Excel 2007, надо установить пакет совместимости (данный модуль можно скачать с сайта Microsoft, установить)

Ограничения Excel

Память – буфер для формул и сводных таблиц увеличен до 2Гб (1Гб в Excel 2003) Сортировка – уровни сортировки увеличены до 64 (3 в Excel 2003)

Автофильтр – выпадающий список до 10 000 элементов (1 000 в Excel 2003)

Максимальная длина формулы – до 8 000 знаков (1 000 в Excel 2003) Вложенность функций – до 64 (7 в Excel 2003)

Условное форматирование – бесконечное количество критериев (3 в Excel 2003) Предельное количество форматов ячеек в книге – до 64 000 (4 000 в Excel 2003) Количество цветов – до 4,3 млн. (56 в Excel 2003)

Количество символов в ячейке – до 32 000 (1 024 в Excel 2003) Количество откатов (Undo) – до 100 (16 в Excel 2003)

Интерфейс программы

Все инструменты сгруппированы на Ленте (Ribbon) по принципу общего действия. Доступ к наборам инструментов – Вкладки (Tabs) .

В добавление к постоянным вкладкам, динамически появляются вкладки, связанные с выделенным объектом (такие вкладки называют контекстными).

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

Кнопка Офис – новое представление меню Файл, содержит в себе новые возможности наряду с прежними командами меню Файл.

В меню Офис кнопка Параметры Excel (Excel Options) – вызов окна настроек программы, которое вобрало в себя три знакомых по прежним версиям окна: Сервис –Параметры (Tools –Options),

Надстройки (Add ins), Настройка (Customize).

Добавление кнопок на панель Быстрого доступа:

1. Выбрать Офис – Параметры Excel (Excel Options),

2. Перейти в категорию Настройка (Customize),

3. В верхнем списке выбрать группу команд,

4. Выбрать инструмент;

5. Нажать Добавить (Add), затем OK.

Новые возможности

Ручной ввод

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

Горячие клавиши

Если нажать ALT , то на экране появятся подсказки для выбора команд без помощи мыши. Клавиши надо нажимать последовательно .

CTRL + N – Создать,

CTRL + O – Открыть,

Режим просмотра Разметка страницы (Page Layout View)

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

Для перехода в режим Разметки страницы надо:

1. Выбрать вкладку Вид (View);

2. В группе Режимы просмотра книги (Workbook Views), выбрать Разметка страницы (Page Layout).

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

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

Как и прежде располагается внизу окна программы. Легко настраивается, – опции подсказок можно настраивать, кликнув правой кнопкой по строке состояния. Здесь появляются кнопка и ползунок изменения масштаба

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

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

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

Ввод данных

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

В каждой ячейке листа может находиться:

1. ТЕКСТ (выравнивание по умолчанию – левый край ячейки)

2. ЧИСЛО (выравнивание по умолчанию – правый край ячейки)

a. Обычное (в российском языковом стандарте разделитель целой и дробной части запятая, а в английском – точка)

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

этого надо в разделе Дополнительно (Advanced) окна Параметры Excel (Excel Options) выключить Использовать системные разделители

(Use system separator), вписать символ разделителя.

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

Очень большие числа автоматически представляются в экспоненциальном формате: 1,2 Е+19 = 1,2*1019 или 1,2 Е­19 = 1,2*10­19 . Наибольшее положительное число 9,9 Е+307 ; наименьшее положительное число 1 Е­307 (числа, большие 9,9 Е+307 становятся текстом, меньшие 1 Е­307 – нулем).

b. Дата (начальная дата – 01.01.1900, конечная – 31.12.9999).

Разделитель в записи дат – слеш день/месяц/год или точка.

01.01.1900=1, 02.01.1900=2, … 31.12.9999=2 958 465

Можно изменить первую дату на 02.01.1904, включив Использовать систему дат 1904 (Use 1904 date system) разделе Дополнительно

(Advanced) окна Параметры Excel (Excel Options).

c. Время (от 00:00:00 до 23:59:59) – дробная часть дня: 12:00=0,5.

Если количество часов больше 23, введенное время преобразуется в формат «дата время». Например, 24:12:15 = 01.01.1900 0:12:15

Изменить системные языковые стандарты можно в Панели управления (Пуск –Настройка –Панель управления) в группе «Язык и региональные стандарты». Эти настройки влияют на работу программ и охватывают кроме чисел денежную единицу, форму записи даты и времени.

3. ФОРМУЛА . Формулы начинаются со знака = (равно), могут содержать круглые скобки (приоритет операций), ссылки на ячейки, имена ячеек и диапазонов, знаки операций, обращения к функциям и др.

Диапазон ячееек: A1:A25 (все от A1 и до A25 , символ : двоеточие )

Диапазон ячееек: A1:С25 (все от A1 до A25, от B1 до B25, от C1 до C25)

Группа ячеек: A1;A25 (2 ячейки A1 и A25, символ ; точка с запятой )

По умолчанию в ячейке с формулой отображается ответ (число или текст),

в Строке формул – формула. Данный режим можно изменить, включив

Показывать формулы, а не их значения (Show formulas in cell instead…)

в разделе Дополнительно (Advanced) окна Параметры Excel (или «Ctrl »+«` »)

Переход между типами ссылок – клавиша F4 на клавиатуре:

W50 F4 $W$50 F4 W$50 F4 $W50 F4 W50

Связывание листов одной книги

При вводе формулы надо:

1. Перейти на нужный лист (щелкнуть по ярлычку листа);

3. Ввести знак следующей операции

Связывание рабочих книг

Предварительно надо Открыть связываемые книги и При вводе формулы :

1. Перейти в нужную книгу, лист (откуда берется ссылка для формулы);

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

3. Ввести знак следующей операции или Enter (для завершения формулы).

На вкладке Данные (Data) в группе Подключения (Connections) кнопка

Изменить связи (Edit Links) позволит обновить, изменить, разорвать связь. При разрыве связи, формулы и внешние ссылки безвозвратно заменяются их значениями (текстом или числом на момент разрыва).

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

Создание имен

1 способ . Строка формул

2. В поле Имя (левая часть строки формул) ввести новое имя ;

3. Нажать Enter

Длина имени до 255 символов. 1 й символ : буква, _ (подчеркивание) или \ (обр. слеш) , далее могут идти буквы, цифры, точки и подчеркивания. Без пробелов ! Запрещены имена в виде ссылок на ячейки (например , Z$100 или R1C1).

2 способ . Из заголовков строк и столбцов таблицы

1. Выделить диапазон, включая заголовки строк и столбцов;

2. На вкладке Формулы (Formulas) в группе Определенные имена (Defined Names) выбрать команду Создать из выделенного (Create from Selection);

3. Указать расположение заголовков (строка выше/ниже, столбец слева/справа).

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

3 способ . Диалоговое окно Диспетчер Имен

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

2. Вкладка Формулы (Formulas), выбрать Диспетчер Имен (Name Manager);

3. Нажать кнопку Создать (New), в появившемся окне Создание имени :

a. Ввести Имя ,

b. Определить Область действия

(в пределах листа / книги),

c. Ввести

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

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

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

Описание основных элементов документа и инструкция для работы с ними в нашем материале.

Кстати, чтобы эффективнее работать с таблицами Exel можете ознакомиться с нашим материалом .

Работа с ячейками. Заполнение и форматирование

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

Файл Эксель состоит из одного или нескольких листов, разграфленных на мелкие ячейки.

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

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

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

Рис. 1 – пример заполнения ячеек

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

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

Выберем ячейку «Доля акций» . Адрес её расположения – А3. Эта информация указана в открывшейся панели свойств. Также мы можем увидеть содержимое.

Формул у этой клетки нет, поэтому они не показываются.

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

Кликните на клеточку правой клавишей манипулятора.

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

Рис. 2 – контекстное меню ячейки и ее основные свойства

Сортировка данных

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

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

Как бы вы это сделали? Банальное перепечатывание таблицы – это лишняя работа, к тому же, если она объемная, делать этого никто не будет.

Для сортировки в Excel есть специально отведенная функция. От пользователя требуется только:

• Выделить таблицу или блок информации;
• Открыть кладку «Данные» ;
• Кликнуть на иконку «Сортировка» ;

Рис. 3 – вкладка «Данные»

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

Рис. 4 – настройка параметров сортировки

Произойдет автоматическая сортировка данных:

Рис. 5 – результат сортировки цифр в столбце «Январь»

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

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

Главное преимущество Excel – возможность автоматического проведения расчетов в процессе заполнения таблицы. К примеру, у нас есть две ячейки со значениями 2 и 17. Как в третью ячейку вписать их результат, не делая расчеты самостоятельно?

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

Затем нажмите на значок функции f(x), как показано на рисунке ниже.

В открывшемся окошке выберите действие, которое хотите применить. СУММ – это сумма, СРЗНАЧ – среднее значение и так далее.

Полный список функций и их наименований в редакторе Excel можно найти на официальном сайте компании Microsoft.

Нам нужно найти сумму двух ячеек, поэтому нажимаем на «СУММ» .

Рис. 6 – выбор функции «СУММ»

В окне аргументов функции есть два поля: «Число 1» и «Число 2» . Выберите первое поле и кликните мышкой на ячейку с цифрой «2».

Её адрес запишется в строку аргумента.

Кликните на «Число 2» и нажмите на ячейку с цифрой «17». Затем подтвердите действие и закройте окно.

Если необходимо выполнить математические действия с тремя или большим количеством клеточек, просто продолжайте вводить значения аргументов в поля «Число 3» , «Число 4» и так далее.

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

Рис. 7 – результат выполнения подсчетов

Создание таблиц

В экселевских таблицах можно хранить любые данные.

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

Имеют преимущество перед аналогичной опцией в и других офисных программах.

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

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

Чтобы создать таблицу, следуйте инструкции:

• Откройте вкладку «Вставка» . В левой части панели опций выберите пункт «Таблица» . Если вам необходимо провести сведение каких-либо данных, выбирайте пункт «Сводная таблица»;
• С помощью мышки выделите место на листе, которое будет отведено для таблицы. А также вы можете ввести расположение данных в окно создания элемента;
• Нажмите ОК , чтобы подтвердить действие.

Рис. 8 – создание стандартной таблицы

Чтобы отформатировать внешний вид получившейся таблички, откройте содержимое конструктора и в поле «Стиль» кликните на понравившийся шаблон.

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

Рис. 9 – форматирование таблицы

Результат заполнения таблицы данными:

Рис. 10 – заполненная таблица

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

Работа с Экселем: самоучитель. Excel (Эксель) - одна из базовых программ пакета Microsoft Office. Это незаменимый помощник при работе с накладными, отчетами, таблицами.
Excel (эксель) позволяет:
программировать, хранить огромные объёмы информации
Строить графики и анализировать результаты
Быстро делать расчеты
Эта программа является отличным выбором для офисной работы.
Начало работы с Excel (эксель)
1. Два раза щёлкнув по названию листа, входим в режим редактирования. В этой панели можно добавить в книгу новый лист, удалить ненужный. Сделать это легко - нужно нажать правой кнопкой мыши и выбрать строку «Удалить».

2. Создать еще одну книгу просто - выбираем в Меню «Файл» строку «Создать». Новая книга расположится поверх старой, а на панели задач появится дополнительная вкладка.

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

Простая F=ma – это формула, сила равна произведению массы и ускорения.

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

Если поставить курсор мышки на любую ячейку и нажать на пункт «выбрать функцию», то появляется мастер функций.

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

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

В случае если функция неизвестна пользователю, то он может установить категорию «полный алфавитный перечень».

Например, дана задача, найти функцию СУММЕСЛИМН. Для этого нужно зайти в категорию математических функций и там найти нужную.

Функция ВПР

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

После чего осуществляется возврат итогового значения из ячейки, которая располагается на пересечении выбранной строчки и столбца.

Вычисление ВПР можно проследить на примере, в котором приведен список из фамилий . Задача – по предложенному номеру найти фамилию.

Применение функции ВПР

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

Второй аргумент А1:В10 – это диапазон, в котором осуществляется поиск.

Третий аргумент – это порядковый номер столбца, из которого следует возвратить результат.

Вычисление заданной фамилии с помощью функции ВПР

Кроме того, выполнить поиск фамилии можно даже в том случае, если некоторые порядковые номера пропущены.

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

Поиск фамилии с пропущенными номерами

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

Он имеет только два значения – «ложь» или «истина». Если аргумент не задается, то он устанавливается по умолчанию в позиции «истина».

Округление чисел с помощью функций

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

А полученное значение можно использовать при расчетах в других формулах.

Округление числа осуществляется с помощью формулы «ОКРУГЛВВЕРХ». Для этого нужно заполнить ячейку.

Первый аргумент – 76,375, а второй – 0.

Округление числа с помощью формулы

В данном случае округление числа произошло в большую сторону. Чтобы округлить значение в меньшую сторону, следует выбрать функцию «ОКРУГЛВНИЗ».

Округление происходит до целого числа. В нашем случае до 77 или 76.

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

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

Вся правда о формулах программы Microsoft Excel 2007

Формулы EXCEL с примерами - Инструкция по применению