Excel2007

Справка

Для открытия окна справки нажмите F1.

При записи десятичной дроби используется запятая.

Для удаления листа или диаграммы:

Щелкнуть правой кнопкой на ярлыке листа (внизу, слева от строки состояния), Удалить, Удалить.

Абсолютные и относительные ссылки в стиле А1

Абсолютная ссылка (например, $В$2) всегда указывает на одну и ту же фиксированную ячейку независимо от того, куда и каким образом вы ее копируете или перемещаете. Ссылка типа В2 является относительной. Если в ячейке С4 содержится формула, использующая содержимое ячейки В2 (с относительной ссылкой В2), и затем вы копируете С4 в ячейку С6 (сдвиг на две позиции), то адрес ссылки изменится (при вычислении по формуле С6 будет использоваться содержимое ячейки В4, т.е., также сдвиг на две позиции). Таким образом, относительность ссылки сохраняется при копировании содержимого ячейки. Однако, относительная ссылка становится абсолютной, если используется операция перетаскивания, или комбинация вырезания и вставки.

В Excel помимо стиля ссылок А1 возможен стиль, обозначаемый R1C1. После буквы R (row) указывается количество строк, а после буквы С (column) – количество столбцов, отсчитываемых от ячейки, в которой расположена формула до ячейки, на которую указывает ссылка. По умолчанию (как и в стиле А1) все ссылки относительны. Относительные ссылки R1C1 (в отличие от А1) сохраняют свою относительность не только при копировании, но и при перемещении.

Переход со стиля ссылок А1 на стиль R1C1

Кнопка MS Office, параметры Excel (внизу), Формулы (слева), поставить галочку на стиль ссылок R1C1.

Диапазоны ячеек и операторы ссылок

Ссылку в Excel можно установить не только на ячейку, но и на диапазон ячеек и на несколько диапазонов ячеек одновременно. При использовании оператора типа С1:Е3 ячейка С1 является верхней левой а ячейка Е3 – нижней правой ячейкой выделяемого диапазона. Выражение С1:Е3; D4:E5 означает объединение дипазонов С1:Е3 и D4:E5.

Операторы сравнения = , > , <, >= , <= , <> (не равно)

Позволяют выполнить сравнение двух операндов, результатом которого является логичское значение ИСТИНА или ЛОЖЬ. Например, если ввести в ячейку: =4=3, то получим ЛОЖЬ, =4>3, то получим ИСТИНА.

Способы выделения объектов таблицы

Щелчок мыши на заголовке столбца выделяет столбец целиком, на номере строки – выделяет строку.

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

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

Копирование и (перемещение) ячеек и формул:

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

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

Построение ряда чисел с постоянным шагом (a_i+1- a_i= h)

- С помощью маркера можно построить ряд данных. Если выделить две ячейки, например, с двумя числами 1 и 1,2, и затем протащить маркер заполнения, то получиться ряд чисел: 1 1.2, 1.4, 1.6 , …

- В А1 ввести 1, далее на Главная-Редактирование щелкнуть Заполнить (слева под знаком ∑), Прогрессия, далее выбрать нужный вариант (например, По столбцам, арифметическая, шаг=0,2 предельное значение, ОК.

Поиск, фильтрация и редактирование в списках

Поиск производится на основе задаваемых критериев – требований.

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

Поиск и фильтрацию можно осуществить с помощью автофильтра и расширенного фильтра.

Критерий может состоять из одного значения. В критериях на основе сравнения можно применять операции: =, >, >=, <, <=, <> (не равно).

При поиске близкого соответствия применяются символы * и ?. * заменяет любое количество символов, ? заменяет один символ. Например, при поиске всех фамилий, начинающихся на Ив, можно использовать шаблон «Ив*».

Автофильтр.

Поместить курсор в таблицу и нажать кнопку Фильтр на панели Сортировка и Фильтр (вкладки Данные). В строку с заголовками столбцов помещаются кнопки раскрывающихся списков (кнопки со стрелками). Нажатие на кнопку открывает меню фильтрации: Сортировка (по убыванию, …) и (если столбец содержит числа) Числовые фильтры (=, >=, первые 10, …) или Настраиваемый фильтр, включающий задание (в окне Пользовательский автофильтр) условий отбора (можно использовать одно условие с логической операцией И (или ИЛИ)).

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

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

Фильтровать данные можно по нескольким столбцам одновременно.

Для снятия всех галочек нужно выбрать Выделить Все.

Чтобы выключить автофильтр нужно снова нажать кнопку Фильтр на панели Сортировка .

Расширенный фильтр.

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

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

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

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

Чтобы воспользоваться расширенным фильтром: на Данные - Сортировка щелкнуть Фильтр , Дополнительно. В появившемся окне Расширенный фильтр указать диапазон списка и диапазон условий. Если выбрать скопировать результат в другое место, то нужно будет задать диапазон, в который будут помещены отфильтрованные данные.

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

Пример 1: Имя Отчество - 1-я строка диапазона условий

Иван 2-я строка диапазона условий

Иванович 3-я строка диапазона условий

В этом случае будут отобраны люди с именем Иван или с отчеством Иванович.

Пример 2: Фамилия 1-я строка диапазона условий

А 2-я

Б 3-я

В 4-я

В этом случае будут отобраны люди, чья фамилия начинается или на букву А, или на Б, или на В.

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

Пример 3: Имя Отдел Дата

Иван Бухгалтерия >01.01.1970

В этом случае будут отобраны люди с именем Иван, работающие в бухгалтерии, с датой рождения > 01.01.1970 .

Одновременное использование И и ИЛИ

Пример 4: Фамилия Отдел

А Бухгалтерия

Б ПФО

В Бухгалтерия

В этом случае будут отобраны люди с фамилиями на А, работающие в бухгалтерии, и люди с фамилиями на Б, работающие в ПФО..

Справка по математическим функциям:

F1, Математические функции, Математические и тригонометрические функции.

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

Встроенные функции

ABS() – абсолютное значение, СУММ() – сумма, ПРОИЗВЕД() – произведение, ПИ() – число π =3,1415…, КОРЕНЬ() – квадратный корень, ФАКТР(4) , равный 1*2*3*4, LN()- натуральный логарифм, LOG10()- десятичный логарифм, EXP() – экспонента, СТЕПЕНЬ(x,a) = x^a, SIN,COS, TAN (тангенс) ,ASIN (арксинус),ACOS (арккосинус),ATAN (арктангенс).

Например, вычисление синуса : вкладка Формулы, Вставить функцию ( появится окно Мастер функций), далее Категория, Математические, SIN. Внизу окна Справка по этой функции.

Иначе: щелчок на значке fx слева от строки формул, ( появится окно Мастер функций).

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

Функции для анализа списка

Эти функции дают информацию об элементах списка, которые удовлетворяют некоторым условиям. Позволяют задавать условия (правда, сравнительно простые) непосредственно в формуле.

Функция СЧЕТЕСЛИ(диапазон; условие) подсчитывает количество элементов, удовлетворяющих условию.

Пример: 1. необходимо подсчитать количество сотрудников с именем Иван

=СЧЕТЕСЛИ(B2:B7; “Иван”)

2. необходимо подсчитать количество двоек в ряде чисел: =СЧЕТЕСЛИ(B2:B7; 2)

Функция СУММЕСЛИ(диапазон; условие; диапазон суммирования) подсчитывает количество элементов, удовлетворяющих условию.

Вычисления в таблицах:

Ввод формулы в ячейку:

Формула начинается со знака =, например, =C3*D3 (содержимое ячейки С3 умножается на D3 ;результат отображается в ячейке с формулой).

Можно также использовать готовую встроенную функцию, которая выбирается из списка.

При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой (ПРОИЗВЕД(A1;B2;C4)). Однако, любой аргумент может быть диапазоном (СУММ(A1:A3;B2:B4) – суммирует все числа в отмеченном диапазоне ).

Некоторые функции могут не иметь аргументов (например, ПИ() – возвращает значение 3,141592…).

Кнопка Автосумма (например, суммирование значений)

Выделить диапазон (столбец или строку). На Формула-Библиотека функций щелкнуть на треугольнике списка Автосумма, щелкнуть Сумма. В свободной ячейке рядом с выбранным диапазоном появится значение искомой суммы.

Аналогично находятся среднее значение, максимальное и минимальное значения.

Суммирование по строке (или по столбцу)

Выделить ячейку в конце строки, f_x, выбрать Математические, выбрать СУММ,ОК,ОК.

То же можно сделать набрав формулу СУММ(диапазон).

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

Пусть таблица содержит данные по продажам телевизоров (строка – продажа), в столбце В находятся названия телевизоров, а в столбце С – количество купленных штук. Требуется подсчитать (и поместить результат в D12), сколько всего было продано телевизоров Panasonic (в таблице имется несколько таких моделй: PanasonicTX-32LE, Panasonic32LM, …):

Курсор в D12, вызвать мастер функций нажав f_x в строке формул, выбрать в категории Математические функции функцию СУММЕСЛИ, ОК. В открывшемся окне в верхнем поле ввести диапазон в столбце В (например, B4:B11), ниже –критерий отбора (“ Panasonic*”, здесь звездочка заменяет любой набор символов), ниже - диапазон суммирования (С4:С11). В результате формула будет иметь вид: СУММЕСЛИ (B4:B11; “ Panasonic*”; С4:С11). Нажать ОК.

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

Отличия от предыдущего случая (с функцией СУММЕСЛИ)

-в открывшемся окне Аргументы функции вначале видны два поля. После заполнения поля Диапазон условия1 открывается поле Условие 1 и т.д.;

-в формуле для функции порядок аргументов – другой.

Работа с массивами. Матричное умножение А*В

Умножить матрицу А на матрицу В возможно, если длина строки левой матрицы А равна длине столбца правой матрицы В.

Пример. А = 1 2 и В = 4 3 Заносим элементы А в диапазон A1:B2, а элементы В – в

3 4 2 1

диапазон D1:E2. Результирующая матрица будет иметь ту же размерность. Выделяем курсором для нее диапазон G1:H2. В строку формул вносим =МУМНОЖ(A1:B2;D1:E2), нажимаем F2 (без ОК), нажимаем Ctrl+Shift+Enter. В ячейках G1:H2 появятся матричные элементы произведения A*B.

Логические функции И и ЕСЛИ

Функция И(Аргумент1; Арг2;Арг3; …) дает значение ИСТИНА, если все аргументы имеют значение ИСТИНА, в противном случае дает ЛОЖЬ.

Функция ЕСЛИ(Логич_выражение; Значение_если_истина; Значение_если_ложь) дает значение Значение_если_истина , если Логич_выражение привычислении дает ИСТИНА (в противном случае функция принимает Значение_если_ложь).

все аргументы имеют значение ИСТИНА, в противном случае дает ЛОЖЬ.

Пример. ЕСЛИ(4>5; ИСТИНА;ЛОЖЬ) даст значение ЛОЖЬ.

Алгебраические операции с логическими операндами

ИСТИНА*ИСТИНА=1; ИСТИНА+ИСТИНА=1;

ИСТИНА*ЛОЖЬ=0; ИСТИНА+ЛОЖЬ=1

Перемножение элементов нескольких столбцов и суммирование по строкам с помощью СУММПРОИЗВ

- Пусть в диапазоне A1:B7 находятся числа. СУММПРОИЗВ(A1:A7;B1:B7) дает сумму произведений Ai*Bi по семи первым строкам.

- Пусть в A8 находится число 17, а в B8 – 10. СУММПРОИЗВ(A1:A7;B1:B7;( A1:A7<A8)* ( B1:B7<B8)дает сумму произведений Ai*Bi только по тем строкам , в которых Ai<17 и Bi < 10.

Третий аргумент функции СУММПРОИЗВ соответствует столбцу, состоящему из нулей и единиц.

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

- СРЗНАЧ(A1:G1) вычисляет среднее арифметическое семи первых чисел в 1-й строке,

СРЗНАЧ(A1:G2) вычисляет среднее арифметическое 14-ти чисел в 1-й и 2-й строках.

- СРЗНАЧ(A1:A7) вычисляет среднее арифметическое семи первых чисел в 1-м столбце.

- СРЗНАЧ(A1:B2;D1:D2) вычисляет среднее арифметическое чисел в двух выделенных диапазонах.

- МАКС(A1:D4) выводит наибольшее число в заданном диапазоне.

- ЛИНЕЙН(A2:C2;A1:C1) дает значения коэффициентов в формуле y=kx+b, приближающей (по методу наименьших квадратов) функцию, заданную таблицей A2:C2;A1:C1. В 1-й строке – значения аргумента x, во 2-й – y.