5. Выделите столбец А и нажмите кнопку автосуммирование.
6. В ячейке А7 будет суммирование по столбцу.
7. Содержимое ячейки перетащите по строке D7.
Упражнение № 10
1. Удалите содержимое строки 7 и столбца D (выделите сначала строку 7 и нажмите клавишу Del, затем выделите столбец D и нажмите Del).
2. Подсчитайте ИТОГИ “на уголок” в ячейку D7.
3. Использование массивов в формулах.
Массивы можно использовать для создания формул, которые возвращают некоторое множество результатов или оперируют на множестве значений. Более того, многие статистческие функции используют только массивы. Расчеты в бухгалтерском учете реализуются также формулами массива. Формула массива действует на нескольких множествах значений, называемых маасивом аргументов и возвращают одно или несколько значений. Диапазон массива - это блок ячеек,который имеет общую формулу массива.
Массив констант – это специальным образом организованный список констант,которые можно использовать в качестве аргументов в формулах массива. Для понимания массива разберем несколько примеров.
Задача 1. Задана таблица чисел
| А | B | C | D | E | |
| 1 | 10 | 5 | 20 | 30 | 20 |
| 2 | 10 | 15 | 30 | 20 | 10 |
| 3 | 20 | 10 | 10 | 50 | 40 |
| 4 | 40 | 30 | 60 | 100 | 70 |
Требуется подсчитать сумму в строках 1,2,3 суммы для каждого столбца, введч одну единственную формулу.
1. Выделить диапазон А4: Е4
2. Ввестиформулу=A1:E1+A2:E2+A3:E3
3. Нажать сочетание клавиш CTRL+SHIFT+ENTER
4. EXCEL в строке формул выведет {=A1:E1+A2:E2+A3:E3}
Полученная формула существует сразу в пяти ячейках, т.е. формулу можно видеть только тогда, когда бужет выделен весь диапазон результата.
Задача 2. Задана таблица чисел. Найти произведение столбцов (А+В)*С. Результат запишите в столбец D.
| A | B | C | D | |
| 1 | 10 | 10 | 2 | 40 |
| 2 | 20 | 10 | 3 | 90 |
| 3 | 15 | 5 | 4 | 80 |
| 4 | 10 | 10 | 2 | 40 |
1. Выделить диапазон D1:D4
2. Записать формулу =(А1:А4+В1:В4)*С1:С4
3. Нажать сочетание клавиш CTRL+SHIFT+ENTER
В пенрых двух задачах мы имели множественный результат. Формула массива позволяет подсчитать ОДИН результат на множестве значений.
Задача 3. По данным таблицы предыдущей задачи найти сумму произведений столбцов
А*В+А*С+В*С
1. Выделить любую ячейку. (Например, Е5)
2. Записать формулу =А1:А4*В1:В4+А1:А4*С1:С4+В1:В4*С1:С4
3. Нажать сочетание клавиш CTRL+SHIFT+ENTER
Задача 4. Использование констант в массиве.
В месяце 22 рабочих дня. Подсчитать фактический заработок работников по формуле
Начислено.=Ставка/К-во рабочих дней в месяце*Отработано дней
Исходные данные представлены в таблице.
| А | В | С | D | |
| 1 | Фамилия И.О. | Ставка | Отраб. дней | Начислено |
| 2 | Заводов А.И. | 380 | 22 | 380.00 |
| 3 | Кимов И.Г. | 450 | 19 | 388.64 |
| 4 | Зеленский И.И. | 800 | 21 | 763.64 |
| 5 | Рогов П.А. | 350 | 22 | 350.00 |
2. Выделить диапазон D2:D5
3. Записать =В2:В5/22*С2:С5
4. Нажать CTRL+SHIFT+ENTER
5. Построится формула {=В2:В5/22*С2:С5}
Некоторые правила для формулы массива.
1.Чтобы ввести формулу массива, сначала выделите ячейку или диапазон, который будет
Содержать результаты. Если формула возвращает несколько значений, то надо выделить диапазон такого же размера, как и исходный.
2. Для фиксации формулы массива надо нажать CTRL+SHIFT+ENTER
3. Нельзя самим вводить фигурные скобки. Иначе EXCEL поймет это как текст.
4. Для изменения формулы массива надо выделить весь массив и активизировать формулу массива.
5. После изменения формулы нажать CTRL+SHIFT+ENTER.
6. Для быстрого выделения массива нажать сочетание клавишCTRL /
4. Использование функций.
Функции EXCEL.
Функции - это специальные, заранее созданные формулы, которые легко и быстро позволяют выполнить сложные вычисления. Они подобны специальным клавишам на калькуляторах.
MicrosoftEXCEL имеет более 300 встроенных функций, в том числе и логических, функций дат, финансовых, математических и т.д. Это категории функции. Каждой категории соответствует свой набор функций. Сведения о функциях можно получить из Справочной системы (?)
Синтаксис функций. Функция состоит из двух частей- имени и аргумента.
Запись функции ,также как и формулы, начинается со знака “=”.
ИМЯ функции описывает операцию, которую она выполняет. Например СУММ – суммирование ; СРЗНАЧ – нахождение среднего значения и т. д.
АРГУМЕНТЫ (ОПЕРАНДЫ) задают значения или ячейки, над которыми выполняются действия
Аргументы должны быть заключены в скобки. Аргументы могут быть записаны через “:” или через “;”. Например: =СУММ(А1:В12) означает, что суммируются значения ячеек в дипазоне от А1 до В12. , но =СУММ(А1;В12) означает сумму двух яеек А1 и В12 .
| A | B | C | D | E | F | G |
| 1 | Январь | февраль | март | Всего | Среднее за квартал | % март к февралю |
| 2 Затраты | 300 | 250 | 430 | |||
| 3 Приход | 800 | 550 | 730 | |||
| 4 Прибыль | =В3-В2 | |||||
| 5 Налог наприбыль | В4*20% |
Порядок расчета:
1. Подсчитайте Прибыль и Налог на прибыль.
2. Используя функцию СУММ подчситайте значение “Всего”
3. С помощью функции СРЗНАЧ рассчитайте “Среднее за квартал”
4. Столбец G подсчитывается по формуле: D/C*100%
Требуется подсчитать средние затраты за январь, февраль, март.
1. В ячейке F1 запишите «Среднее за квартал».
2. Выделите ячейку F2, затем на Панели инструментов щелкните по кнопке Мастер функций (FunctionWizard).
3. Откроется диалоговое окно Мастера функций.
4. В списке Категория (FunctionCategory) статистические (Statistical).
5. В окне функция (Function) появится список статистических функций.
6. В этом окне надо выделить строку СРЗНАЧ (AVERAGE) и щелкнуть по кнопке ДАЛЕЕ (NEXT).
7. Откроется окно Мастер функций -шаг 2 из 5 (FunctionWizard -step 2 of 5).
8. Поместить указатель мыши на заголовок диалогового окна, нажать левую кнопку мыши и перетащить окно к нижней границе экрана.
9. Щелкните в поле Число 1 (Number 1).
10. Выделите диапазон ячеек от А2 до D2.
В процессе выделения диапазона в поле диалогового окна «Значение» ((VALUE) указывается средняя величина содержимого выделенных ячеек. В поле Число1 (Number 1) появились адреса A2:D2.
11. Щелкните по кнопке Готово (Finish). В ячейке Е2 появится среднее значение.
12. Выделите ячейку Е2, установите в ней указатель мыши в правый нижний угол и протяните при нажатой левой кнопке мыши в следующую ячейку Е3.
13. Отпустите клавишу мыши. В ячейке Е3 будет средний приход.
14. Остальные вычисления проведите, используя полученные знания в предыдущих упражнениях
Принцип работы с функцией AVERAGE распространяется на все остальные функции.
Математические функции.
В EXCEL имеется целый ряд математических функций, позволяющих выполнять различные специализированные вычисления.
Функция =СУММ (число). Аргумент числа может содержать до 30 элементов, каждый из которых может быть или const или ссылкой на ячейку. Функция =СУММ ( ) соответствует автосуммированию (å). При выполнении СУММ ( ) можно назначить имя диапазону ячеек. Ячейки могут быть и не смежными (чтобы выделить несмежные ячейки, надо, удерживая CTRL щелкнуть мышкой по ячейкам). Чтобы назначить имя диапазону ячеек, надо:
1. Выделить диапазон ячеек
2. Выполнить Вставка®Имя®Определить.
3. Назначить имя диапазону.
4. В дальнейшем в формуле можно использовать это имя.
Задача. Требуется определить итоги лицензирования а/м.
| А | В | С | D | E | F | |
| 1 | Вид собственности | Легковые а/м | Автобусы | Грузовые а/м | опасные грузы | Специальные |
| 2 | Федеральные | |||||
| 3 | Муниципальные | |||||
| 4 | Частная | |||||
| 5 | С иностр. кан. | |||||
| 6 | Итого |
1. Определим имена
2. В В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.