Применение табличного процессора MS Excel для обработки числовой

Применение табличного процессора MS Excel для обработки числовой и текстовой информации, представленной в табличном виде.

Работа с формулами и функциями

Основные определения • Формула – это некое выражение, которое выполняет вычисление между операндами с помощью операторов. • Операнд – это элемент вычисления (константы, функции и ссылки). • Константа – постоянное (не вычисляемое) значение. Может быть числом или текстом. • Функция – заранее созданная формула, выполняющая сложные вычисления по введенным значениям (аргументам) в строго определенном порядке. Функции бывают математическими, финансовыми, статистическими и т. п.

Основные определения • Ссылка – адрес ячейки или диапазона ячеек на табличном поле, в которых содержатся значения. • Операторы – это знак или символ, определяющий тип вычисления в формуле над операндами. В «Excel» используются следующие операторы: – математические, – текстовые, – операторы сравнения – операторы ссылок.

Основные определения • Формула всегда начинается со знака равенства (=), а затем следуют операнды и операторы, например, =24*3, или =А 1+В 2*(С 10 D 8)/СУММ(L 12: H 4), где: 24, 3 – константы; СУММ – функция; А 1, В 2, С 10, D 8, L 12 и H 4 – ссылки; +, , / и * – операторы.

Типы операторов • Математические операторы – это операторы простых действий, а именно сложения, вычитания, умножения и т. д. Оператор Описание + (плюс) сложение – (минус) вычитание или отрицание * (звездочка) умножение / (косая черта) деление % (знак процента) процент ^ (знак крышки) возведение в степень

Типы операторов • Операторы сравнения – это операторы сравнения разных значений, результатом которого является логическое выражение «ИСТИНА» или «ЛОЖЬ» . Оператор Описание = (знак равенства) равно > (знак «больше» ) больше < (знак «меньше» ) меньше >= (знак «больше или равно» ) больше или равно <= (знак «меньше или равно» ) меньше или равно <> (знак «не равно» ) не равно

Типы операторов • Текстовый оператор амперсанд (&) – это оператор объединения нескольких текстовых отрывков в одну строку. • Оператор ссылок – это оператор ссылки на диапазон ячеек. Оператор Описание : (двоеточие) используется между ссылками на первую и последнюю ячейки диапазона. Например, A 10: C 20; ; (точка с запятой) оператор объединения нескольких ссылок в одну, например, СУММ(С 10: В 15; Е 45: Т 30) знак пробела оператор пересечения множеств, который используется для ссылки на общие ячейки двух диапазонов. Например, В 10: D 20 C 15: C 25.

Приоритет операторов • В случае использования в формуле нескольких операторов, они выполняются в следующем порядке: 1. Операторы ссылок. 2. Минус. 3. Процент. 4. Возведение в степень. 5. Умножение и деление. 6. Сложение и вычитание. 7. Текстовый оператор. 8. Операторы сравнение.

Типы ссылок Бывают следующие типы ссылок: • Относительная ссылка – это ссылка в формуле, основанная на относительном расположении ячейки, в которой находится формула, и ячейки, на которую указывает ссылка. Например A 2. • Абсолютная ссылка – это неизменная ссылка в формуле на ячейку, расположенную в определенном месте. Например $A$2. • Смешанная ссылка – это ссылка с использованием либо абсолютной ссылки на столбец и относительной – на строку ($A 1), либо абсолютной ссылки на строку и относительной – на столбец (A$1).

Создание формул Создание простой формулы. 1. Введите символ «=» c клавиатуры в ячейку. 2. Наберите нужную формулу, например =5*6. 3. Нажмите клавиши(завершение создания формулы): – Enter – для перехода вниз по столбцу; – Shift+Enter – для перехода вниз по столбцу; – Tab – для перехода вправо по строке; – Shift+Tab – для перехода влево по строке

Создание формулы с относительными ссылками. 1. Введите символ «=» c клавиатуры в ячейку. 2. Введите(или укажите с помощью мышки) адрес ячейки, содержащую нужные значения. 3. Вставьте в формулу оператор и адрес следующей ячейки(или число). 3. Завершите создание формулы

Создание формул 1. 2. 3. 4. 5. Создание формулы с абсолютными ссылками. Введите символ «=» c клавиатуры в ячейку. Создайте нужную формулу с использованием ссылок. Не закрепляя созданную формулу, щелкните курсором ввода текста в адресном окошке перед адресом той ячейки, которую необходимо сделать абсолютной ссылкой. Нажмите на клавиатуре клавишу F 4. Завершите создание формулы

Создание формулы с использованием имен. 1. Введите символ «=» c клавиатуры в ячейку. 2. Введите нужное имя, оператор, следующее имя. Например, =Количество*Цена_закупа 3. Завершите создание формулы

Создание формулы одновременно в нескольких ячеек. 1. Выделить нужный диапазон ячеек. 2. Ввести формулу в первую ячейку диапазона. 3. Завершите создание формулы сочетанием клавиш «Ctrl+Enter» .

Редактирование формул Способ 1. 1. Выбрать нужную ячейку 2. Перейти в поле формул и отредактировать формулу 3. Нажать клавишу «Enter» . Способ 2. 1. Дважды щелкнуть по нужной ячейки. 2. Отредактировать формулу непосредственно в ячейке. 3. Нажать клавишу «Enter» .

Выделение всех ячеек с формулами 1. В окне открытого листа перейдите к вкладке «Главная» . 2. Раскройте меню кнопки «Найти и выделить» и в списке команд выберите пункт «Формулы» .

Копирование формул в другие ячейки 1 способ: 1. Выделите ячейку с нужной формулой. 2. Используйте любой известный способ копирования (кнопка «Копировать» на вкладке «Главная» , Ctrl+C и т. д. ). 3. Выделите ячейку, куда необходимо вставить формулу. 4. Используйте любой известный способ вставки (кнопка «Вставить» на вкладке «Главная» , Ctrl+V и т. д. ). 5. Закрепите результат щелчком по клавише Enter.

Копирование формул в другие ячейки 2 способ: 1. Выделите ячейку с нужной формулой. 2. Используйте любой известный способ копирования (кнопка «Копировать» на вкладке «Главная» , Ctrl+C и т. д. ). 3. Выделите ячейку, куда необходимо вставить формулу. 4. Перейдите к вкладке «Главная» и в группе «Буфер обмена» раскройте меню кнопки «Вставить» . 5. В списке команд выберите пункт «Формулы» . 6. Закрепите результат щелчком по клавише Esc.

Копирование только результата формулы 1. Выделите ячейку с нужной формулой. 2. Используйте любой известный способ копирования (кнопка «Копировать» на вкладке «Главная» , Ctrl+C и т. д. ). 3. Выделите ячейку, куда необходимо вставить результат формулы. 4. Перейдите к вкладке «Главная» и в группе «Буфер обмена» раскройте меню кнопки «Вставить» . 5. В списке команд выберите пункт «Вставить значения» . 6. Закрепите результат щелчком по клавише Esc.

Создание связи между ячейками 1 способ: 1. Выделите ячейку с исходным значением. 2. Используйте любой известный способ копирования (кнопка «Копировать» на вкладке «Главная» , Ctrl+C и т. д. ). 3. Выделите ячейку, которая будет связанна с исходной. 4. Перейдите к вкладке «Главная» / «Буфер обмена» / «Вставить» . 5. В списке команд выберите пункт «Вставить связь» 6. Закрепите результат щелчком по клавише Esc.

Создание связи между ячейками 2 способ: 1. Выделите ячейку, которая будет связанна с исходной. 2. В строке формул или непосредственно в ячейке введите знак «=» и абсолютный адрес исходной ячейки. 3. Закрепите результат щелчком по клавише Enter.

Замена формулы на полученное значение 1 способ: 1. Выделите ячейку с исходным значением. 2. Используйте любой известный способ копирования (кнопка «Копировать» на вкладке «Главная» , Ctrl+C и т. д. ). 3. Выделите ячейку, в которую необходимо вставить значение. 4. Перейдите к вкладке «Главная» / «Буфер обмена» / «Вставить» . 5. В списке команд выберите пункт «Вставить значение» 6. Закрепите результат щелчком по клавише Esc.

Замена формулы на полученное значение 2 способ: 1. Выделите ячейку с исходным значением. 2. В строке формул выделить формулу и нажать клавишу F 9. 3. Закрепите результат щелчком по клавише Enter.

Отображение формулы непосредственно в ячейках 1 способ: 1. Перейти на Формулы/Зависимости формул. 2. Нажать кнопку «Показать формулы» .

Отображение формулы непосредственно в ячейках 2 способ: 1. Щелкните по кнопке «Office» . 2 Выберите пункт «Параметры Excel» . 3. В окне «Параметры Excel» на вкладке «Дополнительно» / «Показать параметры для следующего листа» выберите в списке лист для настройки, а затем активируйте пункт «Показывать формулы, а не их значения» . 4. Закройте окно кнопкой «ОК» .

Отображение формулы непосредственно в ячейках 2 способ:

Скрыть формулы от просмотра 1 способ: 1. Щелкните правой кнопкой на нужной ячейке. 2. В контекстном меню выберите пункт «Формат ячеек» . 3. В окне «Формат ячеек» / «Защита» активируйте пункт «Скрыть формулы» . 4. Закройте окно кнопкой «ОК» . 5. Перейдите к вкладке «Рецензирование» / «Изменения» щелкните по кнопке «Защитить лист» 6. В графе «Защита листа» / «Пароль для отключения защиты листа» при необходимости введите пароль. 7. В группе «Разрешить всем пользователям этого листа: » отметьте те действия, которые необходимо выполнять на защищенном листе. 8. Активируйте пункт «Защита листа и содержимого заблокированных ячеек» . 9. Закройте окно кнопкой «ОК» .

Скрыть формулы от просмотра 2 способ: 1. В окне открытого листа выделите нужную ячейку. 2. Перейдите к вкладке «Главная» и в группе «Ячейки» раскрой те меню кнопки «Формат» . 3. В списке команд выберите пункт «Формат ячеек» . 4. Далее действуйте, как в первом способе данной инструкции.

Формулы в работе с массивом • Массив – это несколько наборов значений, объединенных общими вычислениями. • Формула массива – это формула, в которой производится несколько вычислений над одним или несколькими наборами значений (аргументами массива), а затем отображающая один или несколько результатов. Формула массива создается по тем же правилам, что и обычная формула, только помещается в фигурные скобки { } и закрепляется сочетанием клавиш Ctrl+Shift+Enter. • Одномерный горизонтальный массив – когда значения находятся в отдельной строке. • Одномерный вертикальный массив – когда значения находятся в отдельном столбце. • Двумерный массив – когда значения находятся в нескольких столбцах или строках.

Создание формулы массива с несколькими ячейками 1. Выделите диапазон ячеек, где будет располагаться формула массива и введите знак (=), например пусть это будут ячейки С 2: С 6. 2. Введите первый нужный диапазон ячеек , например А 2: А 6. 3. Введите необходимый оператор (например, *), а затем следующий диапазон ячеек, например В 2: В 6. 4. Завершите операцию сочетанием клавиш Ctrl+Shift+Enter. 5. Созданная формула примет вид {=А 2: А 6*В 3: В 6}. В каждой ячейке выделеного диапазона будет находится свой экземпляр формулы, которую нельзя удалить отдельно клавишей Delete.

Создание формулы массива с одной ячейкой 1. Выделите ячейку, где будет располагаться формула массива и введите знак (=), например пусть это будет ячейка F 2. 2. Введите нужную формулу массива , например =СУММ(А 2: А 6*В 3: В 6; D 2: D 6*E 2: E 6). 3. Завершите операцию сочетанием клавиш Ctrl+Shift+Enter. 5. Созданная формула примет вид {=СУММ(А 2: А 6*В 3: В 6; D 2: D 6*E 2: E 6)}.

Редактирование формулы массива 1. Изменение формулы с одной ячейкой Выделяем необходимую ячейку с формулой и редактируем формулу в строке формул. Завершите операцию сочетанием клавиш Ctrl+Shift+Enter 2. Изменение формулы с несколькими ячейками Выделяем весь диапазон ячеек с формулой массива и редактируем формулу в строке формул. Завершите операцию сочетанием клавиш Ctrl+Shift+Enter 3. Увеличение диапазона Выделяем весь диапазон ячеек с формулой массива и вносим нужные изменения в ссылках на ячейки в строке формул. Завершите операцию сочетанием клавиш Ctrl+Shift+Enter

Удаление формулы массива 1. Удаление формулы с одной ячейкой Выделяем необходимую ячейку с формулой и удаляем ее с помощью клавиши Delete 2. Удаление формулы с несколькими ячейками Выделяем весь диапазон ячеек с формулой массива и удаляем его с помощью клавиши Delete

Константы массива • Константы массива – это компоненты формул массива, которые создаются путем ввода списка элементов внутри фигурных скобок ({ }). Скобки при этом вводятся вручную. Например, ={1; 2; 3; 4; 5}. • Константы массива могут содержать числа, текст, логические значения (ИСТИНА и ЛОЖЬ), а также значения ошибок. Текст необходимо заключать в двойные кавычки ( «). • Константы массива НЕ МОГУТ содержать дополнительные массивы, формулы или функции. Числовые значения НЕ МОГУТ содержать знаки процента, валюты, запятые или кавычки. • При горизонтальном массиве (строка) элементы списка разделены точкой с запятой. • При вертикальном массиве (столбец) элементы списка разделены двоеточием. • При двумерном массиве элементы строк разделены точкой с запятой запятыми, а столбцы – двоеточием. Между собой элементы отделяются пробелом.

Создание константы массива 1. Создание горизонтальной константы Например, ячейки А 1 -С 1 со значениями 1, 2, 3. - выделите вертикальный ряд ячеек - в окошке строки формул введите знак (=), откройте фигурную скобку и введите числа, содержащиеся в выделенном ряде ячеек, разделяя их точкой с запятой, закройте фигурную скобку. ={1; 2; 3} - завершите операцию сочетанием клавиш Ctrl+Shift+Enter 2. Создание вертикальной константы Например, ячейки А 2 -А 4 со значениями 4, 5, 6. - выделите горизонтальный ряд ячеек - в окошке строки формул введите знак (=), откройте фигурную скобку и введите числа, содержащиеся в выделенном ряде ячеек, разделяя их двоеточием, закройте фигурную скобку. ={4: 5: 6} - завершите операцию сочетанием клавиш Ctrl+Shift+Enter

Создание константы массива 3. Создание двумерной константы Например, ячейки А 1 -С 3 со значениями 1, 2, 3, 4, 5, 6, 7, 8, 9. - выделите прямоугольный диапазон ячеек - введите числа, содержащиеся в выделенном диапазоне ячеек, разделяя горизонтальные константы точками с запятыми, а вертикальные – двоеточиями. Между собой горизонтальные и вертикальные константы отделяются пробелом. ={1; 2; 3: 4; 5; 6: 7; 8; 9} - завершите операцию сочетанием клавиш Ctrl+Shift+Enter

Пошаговое вычисление сложной формулы 1. Выделите ячейку содержащую формулу. 2. Перейдите к вкладке «Формулы» и в группе «Зависимости формул» щелкните по кнопке «Вычислить формулу»

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

Пошаговое вычисление сложной формулы • 4. Если подчеркнутая часть формулы в свою очередь является ссылкой на ячейку с другой формулой, то нажмите кнопку «Шаг с заходом» . После этого отобразится формула в дополнительном окне «Вычисление» . • 5. Переходя, таким образом, от одной подчеркнутой части формулы к другой и нажимая кнопку «Вычислить» , просматривайте всю сложную формулу, пока каждая часть формулы не будет вычислена.