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

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

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

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

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

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

Функция ВПР

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Формулы 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, установить)

		Форматы файлов
* . xls		формат книги Excel 97–2003
* . xla		надстройка Excel 97–2003 (VBA, макросы)
* . xlsx		формат книги Excel 2007 по умолчанию без поддержки макросов
* . xlsm –		книга Excel 2007 с поддержкой макросов
* . xlsb		двоичный формат для больших объемов данных
* . xltx		шаблон книги Excel 2007 без поддержки макросов
* . xltm –		шаблон книги Excel 2007 с поддержкой макросов
* . xlam –		надстройка Excel 2007

Ограничения 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 »+«` »)

			Знаки операций
	Операторы			Операция			Результат
Арифметические			Сложение
			Вычитание или признак отрицательного числа
		«звездочка»	Умножение
		«крышка»	Возведение в степень
		«процент»	Процент (1=100%, 0,2=20%)
Сравнения
			Больше или равно
							(True) 5>=0
			Меньше или равно
							(False) 1<>1
		«амперсанд»	Текстовый оператор. Объединение строк (= B2& ” руб. ”)
		«двоеточие»					A1: A100
		«точка с зап.»	Объединение нескольких ссылок в одну: A1 и A100				A1; A100
		«пробел»
			Пересечение. Общие ячейки 2 диапазонов 1:2 9

				При копировании формулы
	Относительн ая
			вниз – W51 , вверх – W49 , вправо – X50 , влево – V50
	Абсолютная
				$ – признак фиксации
	Смешанная		Не изменяется столбец , строка может изменяться
			Не изменяется строка , столбец можето изменяться

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

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

Работа с таблицами и формулами
3. Важная функция Excel (эксель)– удобная работа с таблицами.

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

В этом же окне можно произвести выравнивание в ячейке, это делает вкладка «Выравнивание».

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

Перемещать ячейки легко – в этом поможет иконка «Вырезать» на вкладке Главная

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

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

Чтобы записать такую формулу в Excel (эксель) необходимо начать со знака «=».

Печать документа
5. И главный этап после выполненной работы – распечатка документов.

Если для построенной диаграммы на листе появились новые данные, которые нужно добавить, то можно просто выделить диапазон с новой информацией, скопировать его (Ctrl + C) и потом вставить прямо в диаграмму (Ctrl + V).

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

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

Если выбрать опцию «Копировать только значения» (Fill Without Formatting), то Excel скопирует вашу формулу без формата и не будет портить оформление.

В Excel можно быстро отобразить на интерактивной карте ваши геоданные, например продажи по городам. Для этого нужно перейти в «Магазин приложений» (Office Store) на вкладке «Вставка» (Insert) и установить оттуда плагин «Карты Bing» (Bing Maps). Это можно сделать и по с сайта, нажав кнопку Get It Now.

После добавления модуля его можно выбрать в выпадающем списке «Мои приложения» (My Apps) на вкладке «Вставка» (Insert) и поместить на ваш рабочий лист. Останется выделить ваши ячейки с данными и нажать на кнопку Show Locations в модуле карты, чтобы увидеть наши данные на ней. При желании в настройках плагина можно выбрать тип диаграммы и цвета для отображения.

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

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

1. Выделите диапазон.
2. Скопируйте его (Ctrl + C) или, нажав на правую кнопку мыши, выберите «Копировать» (Copy).
3. Щёлкните правой кнопкой мыши по ячейке, куда хотите вставить данные, и выберите в контекстном меню один из вариантов специальной вставки - значок «Транспонировать» (Transpose). В старых версиях Excel нет такого значка, но можно решить проблему с помощью специальной вставки (Ctrl + Alt + V) и выбора опции «Транспонировать» (Transpose).

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

1. Выделите ячейку (или диапазон ячеек), в которых должно быть такое ограничение.
2. Нажмите кнопку «Проверка данных» на вкладке «Данные» (Data → Validation).
3. В выпадающем списке «Тип» (Allow) выберите вариант «Список» (List).
4. В поле «Источник» (Source) задайте диапазон, содержащий эталонные варианты элементов, которые и будут впоследствии выпадать при вводе.

Если выделить диапазон с данными и на вкладке «Главная» нажать «Форматировать как таблицу» (Home → Format as Table), то наш список будет преобразован в умную таблицу, которая умеет много полезного:

1. Автоматически растягивается при дописывании к ней новых строк или столбцов.
2. Введённые формулы автоматом будут копироваться на весь столбец.
3. Шапка такой таблицы автоматически закрепляется при прокрутке, и в ней включаются кнопки фильтра для отбора и сортировки.
4. На появившейся вкладке «Конструктор» (Design) в такую таблицу можно добавить строку итогов с автоматическим вычислением.

Спарклайны - это нарисованные прямо в ячейках миниатюрные диаграммы, наглядно отображающие динамику наших данных. Чтобы их создать, нажмите кнопку «График» (Line) или «Гистограмма» (Columns) в группе «Спарклайны» (Sparklines) на вкладке «Вставка» (Insert). В открывшемся окне укажите диапазон с исходными числовыми данными и ячейки, куда вы хотите вывести спарклайны.

После нажатия на кнопку «ОК» Microsoft Excel создаст их в указанных ячейках. На появившейся вкладке «Конструктор» (Design) можно дополнительно настроить их цвет, тип, включить отображение минимальных и максимальных значений и так далее.

Представьте: вы закрываете отчёт, с которым возились последнюю половину дня, и в появившемся диалоговом окне «Сохранить изменения в файле?» вдруг зачем-то жмёте «Нет». Офис оглашает ваш истошный вопль, но уже поздно: несколько последних часов работы пошли псу под хвост.

На самом деле есть шанс исправить ситуацию. Если у вас Excel 2010, то нажмите на «Файл» → «Последние» (File → Recent) и найдите в правом нижнем углу экрана кнопку «Восстановить несохранённые книги» (Recover Unsaved Workbooks).

В Excel 2013 путь немного другой: «Файл» → «Сведения» → «Управление версиями» → «Восстановить несохранённые книги» (File - Properties - Recover Unsaved Workbooks).

В последующих версиях Excel следует открывать «Файл» → «Сведения» → «Управление книгой».

Откроется специальная папка из недр Microsoft Office, куда на такой случай сохраняются временные копии всех созданных или изменённых, но несохранённых книг.

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

1. Выделите оба сравниваемых столбца (удерживая клавишу Ctrl).
2. Выберите на вкладке «Главная» → «Условное форматирование» → «Правила выделения ячеек» → «Повторяющиеся значения» (Home → Conditional formatting → Highlight Cell Rules → Duplicate Values).
3. Выберите вариант «Уникальные» (Unique) в раскрывающемся списке.

Вы когда-нибудь подбирали входные значения в вашем расчёте Excel, чтобы получить на выходе нужный результат? В такие моменты чувствуешь себя матёрым артиллеристом: всего-то пара десятков итераций «недолёт - перелёт» - и вот оно, долгожданное попадание!

Microsoft Excel сможет сделать такую подгонку за вас, причём быстрее и точнее. Для этого нажмите на вкладке «Данные» кнопку «Анализ „что если“» и выберите команду «Подбор параметра» (Insert → What If Analysis → Goal Seek). В появившемся окне задайте ячейку, где хотите подобрать нужное значение, желаемый результат и входную ячейку, которая должна измениться. После нажатия на «ОК» Excel выполнит до 100 «выстрелов», чтобы подобрать требуемый вами итог с точностью до 0,001.