Пример 11. Автосуммирование

В MS EXCEL предусмотрен быстрый способ суммирования данных. Для этого на панели инструментов Стандартная присутствует кнопка Автосумма.

1. Открыть файл Примеры.XLS с помощью команды меню Файл -> Открыть.

2. Вставить, если необходимо, новый лист с помощью команды меню Вставка ->Лист.

3. Переименовать новый лист с помощью команды меню Формат -> Лист -> Переименовать, имя листа – Пример 11.

4. Заполнить таблицу товаров, начиная с ячейки А2:

СТАТИСТИКА ПРОДАЖИ

5. Установить курсор в ячейку Е3 и нажать кнопку Автосумма (S) на панели инструментов. Программа вставит функцию =СУММ и адрес диапазона левее выделенной ячейки В3:D3.

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

7. Нажать кнопку Ввод или клавишу Enter. Программа вычислит сумму значений в выделенных ячейках.

8. Скопировать формулу ячейки Е3 в ячейки Е4:Е7.

9. Установить курсор в ячейку В8 и нажать кнопку Автосумма (S) на панели инструментов. Программа вставит функцию =СУММ и адрес диапазона выше выделенной ячейки В3:В7.

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

11. Нажать кнопку Ввод или клавишу Enter. Программа вычислит сумму значений в выделенных ячейках.

12. Скопировать формулу В8 в ячейки С8:F8.

13. Закрыть файл с сохранением с помощью команды меню Файл -> Закрыть.

Пример 12. Встроенные функции Microsoft Excel.
Категория «Ссылки и массивы»

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

– номер строки, номер столбца – R1C1;
– имя столбца, номер строки – А1.

Столбцы обозначаются буквами от А до IV (256 столбцов максимально) или цифрами 1-256, строки – числами от 1 до 65 536. Ссылка на диапазон ячеек задается как ссылка на верхний левый угол диапазона, далее ставится знак двоеточия (:), указывается ссылка на правый нижний угол диапазона.

Примеры использования некоторых функций из категории «Ссылки и массивы»:

1. Открыть файл Примеры.XLS с помощью команды меню Файл -> Открыть.

2. Вставить, если необходимо, новый лист с помощью команды меню Вставка ->Лист.

3. Выполнить переименование листа с помощью команды меню Формат -> Лист -> Переименовать, имя листа – Пример 12.

4. Заполнить значения ячеек в диапазоне С2:Е5.

5. Создать именованный блок для диапазона ячеек С2:Е5 с именем Блок с помощью команды меню Вставка -> Имя -> Присвоить.

6. Функция АДРЕС – адрес ячеек или диапазонов ячеек.

Адрес ссылки выдается в виде текста в двойных кавычках. Ссылка представляется в виде номера строки и столбца, на пересечении которых находится ячейка. Ссылка может быть относительной или абсолютной в определенном стиле (А1 или R1C1), включать имя листа рабочей книги.

Формула в ячейке А1 =АДРЕС(4;5;1;2;"Пример 11") дает ссылку на ячейку 'Пример 11'!$Е$4. В формуле использованы параметры (слева направо): 4 – номер строки, 5 – номер столбца, 1 – абсолютная ссылка, 1 – формат ссылки А1, Пример 11 – имя листа.

7. Функция ДВССЫЛ – значение из ссылки.

Ссылка задается в виде текстовой строки. Например, в ячейке А2 содержится формула вида =ДВССЫЛ (АДРЕС(4; 5; 1; 1); 1). Эта формула дает результат – значение из ячейки Е4 (если лист не указан, используется текущий).

8. Функция ЧСТРОК – определение числа строк в заданном диапазоне ячеек. Например, формула в ячейке A3 =ЧСТРОК(С2: Е5) дает значение 4.

9. Функция ЧИСЛСТОЛБ – определение числа столбцов в заданном диапазоне ячеек. Например, формула в ячейке А4 =ЧИСПСТОЛБ (С2: Е5) дает значение 3.

10. Функция СТОЛБЕЦ – определение начального номера столбца ссылки (диапазона ячеек или именованного блока ячеек). Например, формула в ячейке А5 =СТОЛБЕЦ(С2:Е5) дает значение 3.

11. Функция СТРОКА – определение начального номера строки ссылки (диапазона ячеек или именованного блока ячеек). Например, формула в ячейке А6 =СТРОКА(С2:Е5) дает значение 2.

15. Функция ПРОСМОТР – просмотр данных в блоках ячеек. Функция ПРОСМОТР обеспечивает различные режимы поиска: проверка наличия искомого значения в массиве (если значение существует, выводится само значение, в противном случае – сообщение об ошибке #Н/Д – нет данных); поиск искомого значения в векторе просмотра и вывод соответствующего ему значения из вектора результата. Перед выполнением функции ПРОСМОТР диапазон ячеек сортируется в порядке возрастания значений вектора просмотра.

Например, требуется по заданному значению кода материала – ячейка В1 определить соответствующее этому коду название материала. Коды материалов представлены в ячейках С2:С5, названия – в ячейках D2:D5. В ячейке В6 введена формула =ПРОСМОТР(В1;С2:С5;D2:D5), которая возвращает значение из вектора результата (наименование материала) для найденного в векторе просмотра (код материала) значения, в данном случае – Асбест.

19. Функция ТРАНСП – транспонирование диапазона ячеек. Для «разворота» на 90° блока ячеек используется функция ТРАНСП, которая вводится как массив формул. Например, требуется транспонировать исходный блок ячеек С2:Е5 в ячейки A10:D12. В ячейки A10:D12 вводится формула массива {=ТРАНСП(С2:Е5)}. Для этого надо выделить ячейки A10:D12, ввести формулу и нажать CTRL + SHIFT + ENTER.

20. Закрыть файл с сохранением с помощью команды меню Файл -> Закрыть.

Пример 13. Встроенные функции Microsoft Excel.
Категория «Текстовые функции»

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

1. Открыть файл Примеры.XLS с помощью команды меню Файл -> Открыть.

2. Вставить, если необходимо, новый лист с помощью команды меню Вставка ->Лист.

3. Выполнить переименование листа с помощью команды меню Формат -> Лист -> Переименовать, имя листа – Пример 13.

4. Заполнить ячейки A1:D1, начиная с A1, значениями:

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

6. В ячейку A3 ввести формулу преобразования текста в число: =ЗНАЧЕН("0,23р."). Формула дает результат 0,23. Это обратное преобразование функции ТЕКСТ.

7. В ячейку B3 ввести формулу сцепления текстовых строк:

=СЦЕПИТЬ("ПРИМЕР";" "; "СЦЕПЛЕНИЯ СТРОК ТЕКСТА";
" ";"ДЛЯ ВЫВОДА В ОДНОЙ ЯЧЕЙКЕ"). В результате в ячейке B3 выводится строка текста «ПРИМЕР СЦЕПЛЕНИЯ СТРОК ТЕКСТА ДЛЯ ВЫВОДА В ОДНОЙ ЯЧЕЙКЕ».

8. В ячейку СЗ ввести формулу для определения длины текста в ячейке B3: =ДЛСТР(B3).

9. В ячейку D3 ввести формулу для определения первого вхождения в строку текста в ячейке B3 сочетания букв «СТ» с учетом регистра, поиск вести с начала строки текста: =НАЙТИ("СТ";B3;1). При поиске без учета регистра используется функция ПОИСК.

10. Закрыть файл с сохранением с помощью команды меню Файл -> Закрыть.

Пример 14. Встроенные функции Microsoft Excel.
Категория «Дата и время»

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

1. Открыть файл Примеры.XLS с помощью команды меню Файл -> Открыть.

2. Вставить, если необходимо, новый лист с помощью команды меню Вставка ->Лист.

3. Выполнить переименование листа с помощью команды меню Формат -> Лист -> Переименовать, имя листа – Пример 14.

4. Ввести в столбец А, начиная с ячейки A1, формулы для вычисления:

– текущей даты: =СЕГОДНЯ();

– текущей даты и времени: =ТДАТА();

– даты в числовом формате, аргументы функции задаются по частям (год, месяц, день): =ДАТА (2002; 4; 12);

– перевод даты из текстового формата в числовой формат, аргумент задается как строка текста: =ДАТАЗНАЧ ("12.04.2002");

– определение номера года, месяца и дня для даты, заданной в числовом формате:
=ГОД(37430) дает год 2002,
=МЕСЯЦ(37430) дает месяц 6,
=ДЕНЬ(37430) дает число 23.

5. Закрыть файл с сохранением с помощью команды меню Файл -> Закрыть.

Пример 15. Встроенные функции Microsoft Excel.
Категория «Математические»

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

1. Открыть файл Примеры.XLS с помощью команды меню Файл -> Открыть.

2. Вставить, если необходимо, новый лист с помощью команды меню Вставка ->Лист.

3. Выполнить переименование листа с помощью команды меню Формат -> Лист -> Переименовать, имя листа – Пример 15.

4. Ввести в столбец А, начиная с ячейки A1, формулы:

– определение знака выражения: =ЗНАК(-1234) дает -1, так как число отрицательное;

– округление числа до ближайшего целого нечетного числа: =НЕЧЁТ(166,6667) дает 167;

– округление числа до ближайшего целого четного числа: =ЧЁТН(167,6667) дает 168;

– округление числа до ближайшего меньшего целого числа: =ЦЕЛОЕ(166,6667) дает 166;

– отбрасывание дробной части числа: =ОТБР(166,6667;0) дает 166;

– округление числа до ближайшего целого или до ближайшего кратного указанному значению: =ОКРВВЕРХ(166,6667; 10) вычисляет 170;

– округление числа до ближайшего меньшего подмодулю целого числа: =ОКРВНИЗ(20/120*1000;10) вычисляет 160;

– округление числа до указанного количества десятичных разрядов: =ОКРУГЛ(166,6667;3) вычисляет 166,667;

– округление числа до ближайшего по модулю большего целого: =ОКРУГЛВВЕРХ(166,666667;3) вычисляет 166,667;

– округление числа до ближайшего меньшего по модулю целого: =ОКРУГЛВНИЗ(166,66667;3) вычисляет 166,666.

5. Закрыть файл с сохранением с помощью команды меню Файл -> Закрыть.

Не нашли, что искали? Воспользуйтесь поиском: