Составление формул и работа с ячейками

Составление формул и работа с ячейками

3/21/2007 ИЗУЧАЕМ EXCEL 7.0 14

_________________________________________________________________________________

Лабораторная работа № 2

Тема: Составление формул и работа с ячейками.

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

Microsoft EXCEL предлагает богатые возможности для построения сложных формул.

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

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

Создадим несколько простых формул и посмотрим, как они работают.

Упражнение № 1. Создание простых формул

1.В ячейку А1 ввести = 10*5+20*(20-2*5)

2. Нажмите ENTER.

3. Появится число 250.

4. Набираемая формула отражается в строке формул. (рис. 1)

рис. 1

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

Задание В ячейке B5 Подсчитайте сумму отчислений в соцстрах, если :

Общий фонд заработной платы составил 49521,0 руб.

Начислено премий 12380,3 руб.

Выделено помощи 1233,0 руб.

Отчисления в соцстрах составляют 5,4 % от общей суммы.

Упражнение № 2 Использование ссылок:

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

Например, в ячейку B1 надо записать сумму значений ячеек A5+A6.

Сделаем активной ячейку B1

Запишем =

Выделим A5

В ячейке B1 будет = A5. Наберем +.

Выделим ячейку A6 и нажмем ввод.

Упражнение № 3 Ссылки на ячейки из равных листов.

На листе 1 в ячейке А1 записано 150.

На листе 2 в ячейке а1 записано 200.

В ячейку А2 надо записать сумму этих двух ячеек.

Активизируйте лист 1.

Активизируйте ячейку А2 и запишите в ней =.

Выделите ячейку А1 и наберите +.

Активизируйте в лист 2.

Выделите ячейку а1 и нажмите ввод.

EXCEL вернет Вас в ячейку а2 листа 1.

В строке формул будет:

= A1+Лист2!A1.

Упражнение № 4. Ссылки на ячейки из равных листов (другой вариант).

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

Для этого надо выполнить команду ОКНОНОВОЕ, а затем ОКНОРасположить.

Задание. На листе1 записана информация, изображенная на рис 2.

На лист 2 столбец «Сальдо на начало месяца» перенести значение столбца

«Сальдо на конец месяца» из Листа 1 (рис 2)

лист 1 лист 2

рис 2

Занесите информацию в столбцы А, В, С

В ячейку D3 запишите формулу: =А3+В3-С3

Нажмите ENTER

Установите курсор на маркер автозаполнения и протяните его вниз по столбцу (скопируйте формулу)

Активизируйте ячейку D3 на листе 2

Наберите =

Выделите ячейку D3 листе 1

Нажмите ENTER

Скопируйте формулу

Упражнение № 5. Ссылка на ячейки из других книг.

Открыта книга.

На листе 1 в ячейке а1 набрано 150.

Откройте другую книгу, нажав кнопку на панели инструментов «Создать книгу». ( 1-я кнопка слева).

Занесите в ячейку А1 число 400.

Выполните команду ОкноРасположитьВыберите опцию

«Рядом» («сверху вниз», «слева направо», «каскадом»).

В ячейке а2 надо получить результат от сложения: кн.2яч.а1 + кн.1яч.а1 + кн.1яч.в1.

В ячейке В2 кн.2 набираем =

Выделяем ячейку А1 и набираем +.

Переходим в книгу 1, выделяем А1 и набираем +.

Выделяем В2 и набираем 999

В строке формул будет записано = А1Книга1Лист1!

$A$1+КнигаЛист1$B$1

Упражнение № 6. Работа с текстом.

Текстовые значения можно объединять.

В ячейках

а4 записано Морозова

а5 записано Александра

а6 записано Андреевна

Записать в ячейку а8: Морозова Александра Андреевна.

значения символьных констант или переменных объединяются знаком & - амперсант.

В ячейкеА8 наберите =а4&а5&а6

нажмите ENTER

В ячейке А8 появится =МорозоваАлександраАндреевна. ( без пробелов.)

Наберите в ячейке А10 = А4&' `&А5&' '&А6

Нажмите ENTER

В ячейке A10 появится =Морозова Александра Андреевна. ( c пробелами.)

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

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

Сальдо на конец месяца= Сальдо на начало месяца + поступление - выбытие

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

Имя должно начинаться с буквы / или

В имени могут использоваться только буквы, цифры /

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

В качестве имен могут использоваться 1 буква, только не R и C.

Пробелы заменять на подчеркивание.

Упражнение № 7. Создать имя.

В ячейке А5 записано 12550,85 ( Это цена за 1 единицу основного средства)

В ячейке А6 записано 10 (Это количество)

В ячейку А7 надо записать А5*А6 (Количество*Цена)

Выделить ячейку А5

Зайти в текстовое поле имени.

Записать Цена

Нажать ввод.

Выделить ячейку А6

Зайти в текстовое поле имени

Записать Количество

Нажать ввод.

Выделить ячейку А7

Зайти в текстовое поле имени.

Записать Сумма

Нажать ввод.

Проверить работу формулы

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

Упражнение № 8. Определение имени с использованием команды ИМЯ .

Эта команда позволяет использовать текст в соседних ячейках для присвоения имен и переопределения имен.

№ 1. Например, ячейка А4 содержит имя «изделие 1».

и надо использовать этот текст в качестве имени для ячеек B4:E4.

Выделите диапазон ячеек B4:E4

Выполнить команду:

ВставкаИмяПрисвоитьокно Присвоить имя.

В поле имени появится изделие 1.

В поле формулы появится ссылка

Лист 1!$B$4:$E$4

Нажмите ввод.

При следующем открытии окна «Присвоить имя'. Это имя уже будет в списке имен.

№ 2. Можно задавать имя, не выделяя ячейки.

Например: ячейке B5 надо присвоить имя «Тест».

ВставкаИмяПрисвоитьокно Присвоить имя.

В поле Имя введите Тест.

В поле Формула введите =D20.

Нажать кнопку Добавить.

После этого в поле Формула появится определение имени =Лист1D20.

Можно присваивать несколько имен разным ячейкам, не выходя из окна «Присвоить имя». Каждый раз надо нажимать кнопку «Добавить». OK - закрывает окно.

Определение имен на уровне листа.

ВставитьИмяПрисвоитьокно Присвоить имя.

В окне имя запишем Лист2!Листовое имя.

OK.

Примечание. Листовое имя видно в окне диалога только для данного листа.

Например, если открыть Лист2 и окно «Присвоить имя», то в поле имени

Лист1!Листовое имя видно не будет.

Переопределение имен.

ВставкаИмяПрисвоить.

В поле Имя открывшегося окна выделить имя.

В поле Формула ввести новые ссылки, либо выделить новые ячейки при открытом окне «Присвоить имя».

OK.

Создание имен из текстовых значений.

Можно воспользоваться командой ВставкаИмяСоздать.

EXCEL использует текстовые значения в строке (столбце), для создания имени.

A B C D E

Выделяем диапазон.

A2:E4

ВСТАВКАИмяСоздать

Появится окно диалога «Создать имена».

Установить флажок «в столбце слева».

OK.

Диапазону присвоено имя Изделие 1.

Присвоим имена столбцам.

Выделим диапазон B2:E4.

ВставкаИмяСоздать

Установить флажок в строке выше.

OK.

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

Например: надо просуммировать значения по строке 2:

1.В ячейке E2 набираем: =сумм(

2.ВставкаИмяВставить.

3.В окне «Вставка имени» выбрать Изделие 1.

4.OK.

1. Автосуммирование выполняет:

суммы по строкам;

суммы по столбцам;

суммы по строкам и столбцам.

Упражнение № 9

Составьте таблицу

Выделите строку 1 (щелкните вначале строки мышкой).

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

В ячейке D1 будет сумма по строке.

Выделите ячейку D1 и протащите мышью до D6 (суммирование по строкам размножено).

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

В ячейке А7 будет суммирование по столбцу.

Содержимое ячейки перетащите по строке D7.

Упражнение № 10

Удалите содержимое строки 7 и столбца D (выделите сначала строку 7 и нажмите клавишу Del, затем выделите столбец D и нажмите Del).

Подсчитайте ИТОГИ “на уголок” в ячейку D7.

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

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

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

Задача 1. Задана таблица чисел

Требуется подсчитать сумму в строках 1,2,3 суммы для каждого столбца, введч одну единственную формулу.

Выделить диапазон А4: Е4

Ввести формулу =A1:E1+A2:E2+A3:E3

Нажать сочетание клавиш CTRL+SHIFT+ENTER

EXCEL в строке формул выведет {=A1:E1+A2:E2+A3:E3}

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

Задача 2. Задана таблица чисел. Найти произведение столбцов (А+В)*С. Результат запишите в столбец D.

Выделить диапазон D1:D4

Записать формулу =(А1:А4+В1:В4)*С1:С4

Нажать сочетание клавиш CTRL+SHIFT+ENTER

В пенрых двух задачах мы имели множественный результат. Формула массива позволяет подсчитать ОДИН результат на множестве значений.

Задача 3. По данным таблицы предыдущей задачи найти сумму произведений столбцов

А*В+А*С+В*С

Выделить любую ячейку. (Например, Е5)

Записать формулу =А1:А4*В1:В4+А1:А4*С1:С4+В1:В4*С1:С4

Нажать сочетание клавиш CTRL+SHIFT+ENTER

Задача 4. Использование констант в массиве.

В месяце 22 рабочих дня. Подсчитать фактический заработок работников по формуле

Начислено.=Ставка/К-во рабочих дней в месяце*Отработано дней

Исходные данные представлены в таблице.

Выделить диапазон D2:D5

Записать =В2:В5/22*С2:С5

Нажать CTRL+SHIFT+ENTER

Построится формула {=В2:В5/22*С2:С5}

Некоторые правила для формулы массива.

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

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

Для фиксации формулы массива надо нажать CTRL+SHIFT+ENTER

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

Для изменения формулы массива надо выделить весь массив и активизировать формулу массива.

После изменения формулы нажать CTRL+SHIFT+ENTER.

Для быстрого выделения массива нажать сочетание клавиш CTRL /

Использование функций.

Функции EXCEL.

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

Microsoft EXCEL имеет более 300 встроенных функций, в том числе и логических, функций дат, финансовых, математических и т.д. Это категории функции. Каждой категории соответствует свой набор функций. Сведения о функциях можно получить из Справочной системы (?)

Синтаксис функций. Функция состоит из двух частей- имени и аргумента.

Запись функции ,также как и формулы, начинается со знака “=”.

ИМЯ функции описывает операцию, которую она выполняет. Например СУММ - суммирование ; СРЗНАЧ - нахождение среднего значения и т. д.

АРГУМЕНТЫ (ОПЕРАНДЫ) задают значения или ячейки, над которыми выполняются действия

Аргументы должны быть заключены в скобки. Аргументы могут быть записаны через “:” или через “;”. Например: =СУММ(А1:В12) означает, что суммируются значения ячеек в дипазоне от А1 до В12. , но =СУММ(А1;В12) означает сумму двух яеек А1 и В12 .

Упражнение № 11 Создайте таблицу и выполните расчеты

Порядок расчета:

Подсчитайте Прибыль и Налог на прибыль.

Используя функцию СУММ подчситайте значение “Всего”

С помощью функции СРЗНАЧ рассчитайте “Среднее за квартал”

Столбец G подсчитывается по формуле: D/C*100%

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

В ячейке F1 запишите «Среднее за квартал».

Выделите ячейку F2, затем на Панели инструментов щелкните по кнопке Мастер функций (Function Wizard).

Откроется диалоговое окно Мастера функций.

В списке Категория (Function Category) статистические (Statistical).

В окне функция (Function) появится список статистических функций.

В этом окне надо выделить строку СРЗНАЧ (AVERAGE) и щелкнуть по кнопке ДАЛЕЕ (NEXT).

Откроется окно Мастер функций -шаг 2 из 5 (Function Wizard -step 2 of 5).

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

Щелкните в поле Число 1 (Number 1).

Выделите диапазон ячеек от А2 до D2.

В процессе выделения диапазона в поле диалогового окна «Значение» ((VALUE) указывается средняя величина содержимого выделенных ячеек. В поле Число1 (Number 1) появились адреса A2:D2.

Щелкните по кнопке Готово (Finish). В ячейке Е2 появится среднее значение.

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

Отпустите клавишу мыши. В ячейке Е3 будет средний приход.

Остальные вычисления проведите, используя полученные знания в предыдущих упражнениях

Принцип работы с функцией AVERAGE распространяется на все остальные функции.

Математические функции.

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

Функция =СУММ (число). Аргумент числа может содержать до 30 элементов, каждый из которых может быть или const или ссылкой на ячейку. Функция =СУММ ( ) соответствует автосуммированию (). При выполнении СУММ ( ) можно назначить имя диапазону ячеек. Ячейки могут быть и не смежными (чтобы выделить несмежные ячейки, надо, удерживая CTRL щелкнуть мышкой по ячейкам). Чтобы назначить имя диапазону ячеек, надо:

Выделить диапазон ячеек

Выполнить ВставкаИмяОпределить.

Назначить имя диапазону.

В дальнейшем в формуле можно использовать это имя.

Задача. Требуется определить итоги лицензирования а/м.

Определим имена

В В6: =СУММ (легковые).

Функция =СУММ ( ) достачно гибкая, но при добавлении ячейки в конец (начало) диапазона могут возникнуть трудности.

Например:

1 100

2 200

3 100

4 100

5 200

6 100 - ввести

7 =СУММ (А1:А5)700

Вставим строку 6 и внесем в нее число. Например, 100. Результат не изменится. Надо в ячейку А7 ввести =СУММ (А1:А6).

ABS - возвращает абсолютное значение чисел. Имеет синтаксис =ABS (число).

Например:

В А1 записано - 200, в ячейку А2 введем =ABS (A1). Возвращает значение 200.

Функция ЗНАК возвращает знак числа.

= ЗНАК (число).

Если число больше 0, функция ЗНАК возвращает 1.

Если число меньше , то -1.

Если число =0, то .

Например:

В ячейке Е1 записано =ЗНАК (СУММ(А1:D1)). Возвращает -1.

Функция ОКРУГЛ.

=ОКРУГЛ (число; число_цифра)

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

Формула. Результат.

=ОКРУГЛ (897,457; - 2) 900

=ОКРУГЛ (897,457; - 1) 900

=ОКРУГЛ (897,457; 0) 897

=ОКРУГЛ (897,457; 1) 897,5

Функция ЧЕТН округляет число до ближайшего нечетного числа.

ЧЕТН (число) ; нечетн. (число)

ЧЕТН (5) 6

НЕЧЕТН (7,2) 9

Функция целое.

=целое (число)

=целое (185,37) 185.

=целое (200,99) 200.

Функция корень.

=Корень (число). Возвращает значение квадратного корня.

Функция остаток.

=ОСТАТОК (число; делитель)

возвращает остаток от деления.

=остаток (33;4) 1.

Функция степень.

=степень (число; степень).

log 10 (число) - десятичный логоритм.

log (число; основание).

log (12; 2)

log2 12.

LN (число) - натуральный логоритм.

Текстовые функции.

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

ЗНАЧЕН (текст).

Аргумент ТЕКСТ может быть строкой, заключенной в двойные кавычки или ячейкой в которой содержится текст. Преобразуемые текстовые значения могут быть записаны в любом допустимом формате. Например:

=ЗНАЧЕН (А1)

А1 содержит "0025"

Ответ: 25.

Функция РУБМ.

=ДЛСТР (текст).

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

Задача. Определить длину текста в ячейке А1:.

"Функции EXCEL".

=ДЛСТР (А1)

13

Или в ячейке А1 записано число 156.

=ДЛСТР (А1)

3

Ячейка, на на которую ссылается функция ДЛСТР может содержать другие текстовые функции. В ячейку А1 записано выражение:

=ПОВТОР ("*В"; 25)

=ДЛСТР (А1)

50.

=СЖПРОБЕЛЫ (текст) - удаляет начальные и конечные пробелы из строки и между словами оставляет по одному пробелу. В А1 записано #### Тарасов ##### Алексей ###### Петрович. Записать =СЖПРОБЕЛЫ (А1).

=СОВПАД (текст 1; текст 2) ВА1: Адрес:, 355000, 26, СТАВРОПОЛЬ Г, ВАСИЛЬЕВА УЛ, 45 В С3 записано.

Адрес: , 355000, 26, СТАВРОПОЛЬ, УЛ ВАСИЛЬЕВА, 45.

СОВПАД (А1;С3). Если не совпадет, то ложь.

=ПРОПИСН (текст)

В А1 записано: с: статистические функции.

В А2 записать:

СТАТИСТИЧЕСК ФУНК

=ПРОПИСН (А1)

=СТРОЧНАЯ

=СТРОЧН (А1) - статистичекие функции.

ПРОПНАЧ (текст)

Статистичекие функции

=ПРОПНАЧ (А1).

Работа с элементами строк.

А1 А2 А3

Иванов Петр Иванович

Надо записать в А2

в А4: Иванов П. И.

ЛЕВСИМВ (текст; число знаков)

=ПРАВСИМВ (текст; количество символов)

=ПС (текст; начальная позиция; количество символов)

В А1 запишем 20-11-35, 20 часов 11 минут 35 секунд

Выделить минуты (А1, 4, 2). Ответ: 11

=Сцепить - эквивалент &

Выполнить двумя способами: Иванов Петр Иванович.

Логические функции.

=Если (Условие, выраж_если_истина;выражение_если_ложь)

Задача. Имеем свободную таблицу успеваемости, в которой надо обозначить пометку о назначении стипендии.

В ячейку Е2 записать Если (и (В2 >3; С2 > 3; D2 > 3); "да"; "нет").

Вложенные функции.

Если в ячейке А1 записана сумма вычетов из заработной платы для определения налогооблагаемой суммы. Этот вычет может быть равен одному минимальному зароботку, двум, трем пяти. Каждому из них присвавается код.: 21, 22, 23, 25.

=Если (А1=1; "21"; если (А1=2; "22"; если А1=3; "23"; если А1=5; "25")))).

Допускается до 7 вложений.

Функция ДАТ

Создание упорядоченного ряда. ПравкаСодатьПргрессияокно прогрессияУстановить флажки:

по строкам столбцами секция тип дата.

В секции единицы выбрать день месяц, год.

Ввести с клавиатуры дату: например 25.04.99 и скопировать.

Ввод дат с использованием автозаполнения

вводим начальную дату и за ней следующую

Форматирование дат и времени:

ФорматЯчейкавкладка числоДатаТип.

Арифмитические операции с датами:

а) d + k - количество дней - новая

В ячейку А1: 1.03.99

В2:=1.03.99 + 100 9.06.99.

б) D2-D7 - количество дней.

В ячейке А5 записано: 27.07.99

А6: 31.12.99.

В ячейке В5: =А6-А5157.

в) =Сегодня ( ) - возвращаеттекущее время.

г) ТДАТА ( ) - возвращает текущую дату и время.

д) ДЕНЬНЕД (десятичная дата, тип)

тип = 1, воскр. = 1 день

тип = 2 - счет с понедельника

е) месяц (дата)

ж) год (дата)

е) день(дата)

ЗАДАНИЕ. Выполните расчеты в таблице, используя имена.

1. Заполните таблицу как это показано на рис 4.

рис 4

Присвойте следующие имена

Минимум

Начислено

Налог

Проф

Пенс

Удержано

Выдано

Используя присвоенные имена, формулы и формулы массива, выполните расчеты

Алгоритм расчета :

В ячейке G2 записать 83,49 - это текущий необлагаемый налогом минимум

В столбце Начислено записать исходные суммы начислений

Проф. взносы подсчитать как 1% от Начислено.

Пенс. Отчисления подсчитать как 1% от Начислено.

Налог подсчитать как 12% от (Начислено - Пенс - минимум).

Выполнить функцию округления результата до целых

Удержано подсчитать как Пенс. + Проф. +Налог.

Выдано подсчитать как Начислено - Удержано.

Подсчитайте ИТОГО.