Автозаполнение и сортировка

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

Если несколько первых знаков, введенных в ячейку, совпадают со знаками записи, уже имеющейся в этом столбце, Excel автоматически подставляет недостающую часть записи. В Excel автоматическое завершение выполняется только для тех записей, которые содержат текст или комбинацию текста и чисел. Эта функция не используется для записей, полностью состоящих из чисел, значений даты или времени.

В Excel есть возможность одну и ту же информацию распространить на весь столбец (строку, диапазон) очень быстро. Для этого в первой ячейке столбца (строки) записывается значение, которое нужно размножить. Далее нужно выделить весь столбец (строку или диапазон, на который нужно распространить значение). В меню выбирается команда (рис. 7) Главная → → Заполнить → Вниз (Вправо). Во всех выделенных ячейках будет находиться значение, которое было введено в первую ячейку.

.

Рис. 7

Таким же путем можно заполнить целый ряд столбцов или строк. Например, если в столбце находится несколько числовых значений, которые нужно раскопировать на 10 соседних столбцов, то необходимо выделить все ячейки со значениями и ячейки тех столбцов, в которые нужно поместить информацию из первого столбца и выполнить заполнение вправо (рис. 8).

Рис. 8. Заполнение данными: а – до заполнения, б – после заполнения

В Excel можно заполнять ячейки не только одинаковыми данными, но и изменяющимися с определенной закономерностью. Простейшим способом построения прогрессий в Excel является применение команды Главная → → Прогрессия. Эта команда позволяет заполнять ячейки не только одинаковыми значениями по образцу, но и арифметическими или геометрическими прогрессиями, основанными на заданной величине шага или на автоматической величине шага, если заполнение происходит на основе выделенного диапазона ячеек.

Рис. 9. Заполнение прогрессией

Например, можно ввести два первых значения, затем выделить их и диапазон, который необходимо заполнить, выполнить команду Главная → → Прогрессия. В открывшемся окне нужно выбрать параметры. Как видно на рисунке 9, шаг может определяться автоматически из разности двух заполненных значений, а может быть задан в специальном поле «Шаг» (в этом случае достаточно одного начального значения). После нажатия клавиши «ОК» выделенное поле заполнится соответствующими значениями (рис. 10).

Рис. 10. Результат заполнения прогрессией

Таким же способом можно заполнять ячейки данными типа «Дата». Тогда в группе «Тип» надо выбрать значение «Дата», в группе «Единицы» - единицы в которых указывается шаг прогрессии. Например, чтобы заполнить столбец датами с периодичностью 5 дней, надо переключатель «Единицы» поставить в положение «день», а в строке «Шаг» указать число 5.

Инструмент Автозаполнение работает аналогично прогрессии, но значения, которыми заполняются ячейки, содержатся во встроенных списках. По-умолчанию в Excel содержится четыре списка: дни недели в сокращенной и полной записи, месяцы года в сокращенной и полной записи.

Как воспользоваться функцией автозаполнения? В нижнем правом углу выделенной (активной) ячейки или выделенного диапазона ячеек можно видеть небольшой квадратик — маркер автозаполнения. Если потянуть за маркер правой кнопкой, после ее отпускания откроется контекстное меню, в котором можно найти дополнительные команды для копирования оформления ячеек, создания прогрессий и последовательностей дат. Нужно в одной ячейке поместить начальное значение, а затем навести мышку на указатель автозаполнения, который выглядит как тонкий крестик, за который можно «схватиться» левой или правой кнопкой мыши и потащить вниз, вверх, вправо или влево и заполнить выделенный диапазон значениями.

Списки автозаполнения можно создавать самостоятельно. Для этого нужно выполнить команду меню → Параметры Excel. На вкладке «Общие» (рис. 11) нажать кнопку «Изменить списки» и в окне «Элементы списка» ввести значения нового списка через запятую.

Рис. 11. Добавление собственных списков

С помощью Excel очень удобно сортировать набранные данные. Для этого нужно выполнить следующие действия:

1. Выделите диапазон, который хотите сортировать.

2. Выполните команду Меню=>Данные=>Сортировка. Появится окно “Сортировка диапазона”.

3. Выберите столбец, по которому хотите сортировать. (чтобы сортировать по строкам, нажмите кнопку “Параметры” и поменяйте соответствующий переключатель).

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

5. Нажмите “ОК”.

Контрольные вопросы

1. Назначение программы Microsoft Excel

2. Файл MS Excel. Работа с листами

3. Работа с данными в листах MS Excel

4. Адресация в формате А1

5. Адресация в формате R1C1

6. Формулы, выражения, операторы и операнды

7. Заполнение данными

8. Автозаполнение данными

9. Прогрессия

10. Сортировка данных

Задания

1. Создать новую книгу Excel, удалить «Лист 3», переименовать «Лист 1» в «Формулы», а «Лист 2» в «Типы адресаций».

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

3. В листе «Формулы» в ячейке С1 ввести формулу длины окружности, при этом значение числа π должно быть задано в формуле числом 3,14, а значение диаметра помещено в ячейку А1 и в формулу передаваться ссылкой.

4. Добавить новую строку выше первой, записать в ячейке А1 текстовое значение «Диаметр», в ячейке В1 – «Площадь круга», в ячейке С1 – «Длина окружности».

5. Заполнить столбец А на длину 10 строк значениями, формирующимися по правилам арифметической прогрессии с шагом 1,5, первым значением будет число, записанное в первой ячейке столбца.

6. Заполнить формулами площади круга и длины окружности десять строк столбцов В и С.

7. Перевести адресацию ячеек в формат R1C1. В листе «Типы адресаций» в ячейке С1 ввести формулу, определяющую разность значений, содержащихся в ячейках А1 и В1, используя относительную адресацию. Ввести числовые значения в ячейки В2 и С2, затем скопировать формулу из ячейки С1 в ячейку D2. Проверить результат (результатом должна быть разность чисел в ячейках В2 и С2).

8. При копировании формулы использовать пункт меню «Специальная вставка», где указывать тип «Формула».

Функции MS Excel

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

Чтобы использовать функцию, её нужно ввести как часть формулы в ячейку листа. Формула должна начинаться со знака равенства (=), за которым следует набор величин, операторов и функций. Если функция стоит в самом начале формулы, ей должен предшествовать знак равенства, как и во всякой другой формуле.

Любая функция начинается с ее имени, после которого в круглых скобках следует список аргументов. Этот список может быть также и пустым. Скобки используются для обозначения начала и конца списка аргументов. Скобки должны быть парными, пробелы перед скобками или после них не допускаются. Внутри скобок должны располагаться аргументы. Список аргументов может состоять из чисел, текста, логических величин, массивов, других функций, выражений или ссылок. Задаваемые аргументы должны иметь допустимые для данного аргумента значения, т.е. соответствовать определенному типу. Аргументы могут быть как константами, так и формулами. Эти формулы, в свою очередь, могут содержать другие функции. Функции, являющиеся аргументом другой функции, называются вложенными. В формулах Microsoft Excel можно использовать до семи уровней вложения функций.

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

Чтобы вставить функцию в ячейку можно использовать один из трех вариантов: непосредственно записать ее в ячейку, начиная со знака «=»; использовать пункт меню Функция (рис. 12); добавить функцию в активную ячейку, нажав символ «f_x» перед строкой состояния.

Рис. 12

Для удобства работы функции в Excel разбиты по категориям: функции управления базами данных и списками, функции даты и времени, DDE/Внешние функции, инженерные функции, финансовые, информационные, логические, функции просмотра и ссылок. Кроме того, присутствуют следующие категории функций: статистические, текстовые и математические. В распоряжении пользователя предоставляется более 300.

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

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

В Excel широко представлены математические функции. Например, можно выполнять различные операции с матрицами: умножать, находить обратную, транспонировать и т.д.

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

Функции просмотра и ссылок позволяет «просматривать» информацию, хранящуюся в списке или таблице, а также обрабатывать ссылки.

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

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

Чтобы получить доступ к математическим функциям, воспользуемся пунктом меню Функция. При этом на экране откроется окно «Мастер функций» (рис. 13). В открывшемся окне в меню «Категория» нужно выбрать пункт «Математические». После этого в окне ниже появится список математических функций, из которых выбирают нужную. Функции расположены в алфавитном порядке.

Рис. 13. Мастер функций

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

ФункцияABS возвращает абсолютное значение числа, т.е. число по модулю. Функция ABS имеет один аргумент – действительное число, модуль которого требуется найти.

Синтаксис: ABS(число)

Пример: ABS(-45.3)

Результат: 45.3

Тригонометрические функции содержатся в MS Excel в большом количестве (табл. 1). Каждая тригонометрическая функция имеет один аргумент – угол в радианах. Чаще всего в задачах мы имеем дело с углами, измеряемыми в градусах, поэтому, необходимо освоить функции перевода угловых единиц из градусов в радианы и обратно.

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

Таблица 1

Функция Градусы преобразует радианы в градусы. Аргументом функции является значение угла в радианах. Функция Радианы преобразует градусы в радианы. Аргументом функции является значение угла в градусах.

Например, необходимо найти синус угла в 43°. Для этого нужно выполнить две функции:

=SIN(Радианы(43))

В данном примере использовано вложение функций. При использовании вложений сначала вычисляется самая «внутренняя функция», возвращаемое значение которой будет служить аргументом для более внешней. Т.е. На первом шаге вычислений будет осуществлен перевод градусов в радианы и аргументом функции SIN станет полученное значение: SIN(0,75). Следующим шагом будет вычисление синуса угла 0,75. пользователь не видит всех этих шагов, а сразу получает конечный результат, в данном случае 0,68 (синус угла 43°).

Функция EXP возвращает экспоненту аргумента. Аргументом может быть любое число. Экспонента - число «e», возведенное в указанную степень. Число «e» равно 2,71828182845904 и является основанием натурального логарифма.

Функция LN возвращает натуральный логарифм числа.

Функция LOG возвращает логарифм числа по заданному основанию. Т.о. эта функция имеет два аргумента: число и основание.

Синтаксис: LOG(число; основание).

Число — положительное вещественное число, для которого вычисляется логарифм.

Основание — основание логарифма. Этот аргумент необязательный, т.е. может отсутствовать. Если основание опущено, то оно полагается равным 10.

Функция LOG10 возвращает десятичный логарифм числа.

Функция ЗНАК определяет знак числа. Возвращает 1, если число положительное, ноль (0), если число равно 0, и -1, если число отрицательное. Аргументом является любое вещественное число.

Примеры:

=ЗНАК(-76)

Результат: -1

=ЗНАК(0,65)

Результат: 1

MS Excel предоставляет возможность производить операции над матрицами и содержит три встроенных функции работы с матрицами.

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

Пример:

Введем в ячейки диапазона от А1 до С3 числа – значения матрицы. В ячейку А4 введем формулу =МОБР(А1:С3).

Рис. 14. Вычисление обратной матрицы

Чтобы формула оперировала с массивами данных, ее нужно ввести особым образом. Когда мы вводим обычную формулу, то после ее написания нажимаем клавишу «Enter». При работе с функцией, которая оперирует массивами данных, нужно нажимать комбинацию клавиш «Ctrl+Shift+Enter». И так, после ввода формулы в ячейку А4, нужно выделить диапазон, равный объему обратной матрицы, т.е. такой же как и исходная матрица, начиная с ячейки в которой записана формула. После этого следует нажать клавишу «F2», которая позволяет редактировать формулу и нажать комбинацию клавиш «Ctrl+Shift+Enter». Результат представлен на рис. 14.

Функция МОПРЕД возвращает определитель матрицы. Эта функция, так же как и предыдущая, в качестве аргумента должна получать массив значений – матрицу, определитель которой нужно вычислить, числовой массив с равным количеством строк и столбцов. Массив может быть задан как интервал ячеек, например A1:C3, или как массив констант, например {1;2;3:4;5;6:7;8;9}, или как имя, именующее интервал или массив.

Чтобы задать имя для интервала или одной ячейки, нужно выделить этот интервал или ячейку и нажать правую клавишу мыши, при этом появится меню, в котором нужно выбрать пункт «Имя диапазона» (рис. 15), после чего в открывшемся окне в активном поле ввести имя интервала или ячейки и нажать кнопку «ОК».

Рис. 15. Присвоение имени массиву значений

Если какая-либо ячейка в массиве пуста или содержит текст, то функция МОПРЕД возвращает значение ошибки #ЗНАЧ!. МОПРЕД также возвращает значение ошибки #ЗНАЧ!, если массив имеет неравное количество строк и столбцов.

Определитель матрицы — это число, вычисляемое на основе значений элементов массива. Для массива A1:C3, состоящего из трех строк и трех столбцов, определитель вычисляется следующим образом:

МОПРЕД(A1:C3)=A1×(B2×C3-B3×C2)+A2×(B3×C1-B1×C3)+A3×(B1×C2-B2×C1)

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

Функция МУМНОЖ(массив1;массив2) возвращает произведение матриц (матрицы хранятся в массивах). Результатом является массив с таким же числом строк, как массив1 и с таким же числом столбцов, как массив2. Массив1 и массив2 – аргументы функции, перемножаемые матрицы.

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

Массив1 и массив2 могут быть заданы как интервалы, массивы констант или ссылки. Если хотя бы одна ячейка в аргументах пуста или содержит текст или если число столбцов в аргументе массив1 отличается от числа строк в аргументе массив2, то функция МУМНОЖ возвращает значение ошибки #ЗНАЧ!.

Массив a, который является произведением двух массивов b и c определяется следующим образом:

где i — номер строки, j — номер столбца, n = i∙j.

Формула должна быть введена как формула работы с массивами, т.е. с использованием комбинации клавиш «Ctrl+Shift+Enter».

Функция КОРЕНЬ возвращает положительное значение квадратного корня. Аргументом функции должно быть положительное число.

В MS Excel содержится несколько функций округления.

Функция НЕЧЁТ(число) возвращает аргумент, округленный до ближайшего нечетного целого: положительное в сторону увеличения, отрицательное – в сторону уменьшения.

Примеры:

=НЕЧЁТ(-2)

Результат: -3

=НЕЧЁТ(4)

Результат: 4

=НЕЧЁТ(1,7)

Результат: 3

Функция ЧЁТН(число) возвращает аргумент, округленный до ближайшего четного целого: положительное в сторону увеличения, отрицательное – в сторону уменьшения.

Функция ОКРВВЕРХ(число;точность) – округляет число до ближайшего большего с указанной точностью, т.е. до ближайшего большего числа, кратного аргументу «точность». Аргументы должны иметь одинаковые знаки.

Эта функция применяется тогда, когда некие значения нужно округлить в большую сторону, не учитывая некоторые единицы. Например, если нужно посчитать, сколько килограммов металла потребуется для изготовления 25 заготовок, каждая из которых весит 3,25 кг, то можно сконструировать такую функцию, аргументом которой является арифметическое выражение:

=ОКРВВЕРХ(25*3,25;1)

Результат: 82

Результат умножения 25 на 3,25 равен 81,25

Функция ОКРВНИЗ(число;точность) работает аналогично предыдущей рассмотренной функции, только округление происходит вниз, т.е. до ближайшего меньшего числа, кратного точности.

Функция ОКРУГЛ(число;число_разрядов)округляет число до указанного количества десятичных разрядов. Если число разрядов равно нулю, то число округляется до ближайшего целого. Если число разрядов меньше нуля, то число округляется до точности десятков, сотен и т.д. Рассмотрим работу этой функции на примерах. Округление производится по математическим правилам округления.

Примеры:

=ОКРУГЛ(5,4;0)

Результат: 5

=ОКРУГЛ(5,7;0)

Результат: 6

=ОКРУГЛ(543,7;-1)

Результат: 540

=ОКРУГЛ(8,906;2)

Результат: 8,91

Функции ОКРУГЛВВЕРХ(число;число_разрядов) и ОКРУГЛВНИЗ(число;число_разрядов) аналогичны функции ОКРУГЛ с тем отличием, что округление производится не по математическим правилам, а в большую или меньшую сторону соответственно.

Функция ОСТАТ(число;делитель) возвращает остаток от деления числа на делитель. Результат имеет такой же знак как и делитель. Если делитель равен 0, то функция ОСТАТ возвращает значение ошибки #ДЕЛ/0!.

Функция ОТБР(число;число_разрядов) отбрасывает дробную часть так, что остается целое число. Число_разрядов — число, определяющее точность усечения. Аргумент число_разрядов необязательный. Значение по умолчанию аргумента число_разрядов равно нулю. Если число_разрядов больше нуля, то в результате получится дробное число, если меньше нуля, то получится целое число, округленное до 10, 100 и т.д. рассмотрим работу функции на примерах.

Примеры:

=ОТБР(24,256;2)

Результат: 24,25

=ОТБР(24,256;1)

Результат: 24,2

=ОТБР(24,256;0)

Результат: 24

=ОТБР(24,256;-1)

Результат: 20

Функция ПИ()Возвращает число 3,14159265358979, математическую константу «пи» с точностью до 15 цифр. Т.к. в MS Excel нет встроенной константы для числа «пи», то в формулы и функции можно включать функцию ПИ.

Например, для расчета длины окружности радиусом 5мм вводится формула: =2*ПИ()*5.

Функция ПРОИЗВЕД(аргумент1;…;аргумент30) возвращает произведение аргументов. Обязательным является только первый аргумент, далее может следовать любое количество аргументов, в том числе и нулевое. Аргументами могут быть непосредственно значения, ссылки на ячейки, на массивы ячеек, арифметические выражения и функции, возвращаемым значением которых является число или массив чисел. Аргументы, которые являются логическими значениями или текстовыми представлениями чисел, учитываются. Если аргумент является массивом или ссылкой, то в массиве или ссылке учитываются только числа. Пустые ячейки, логические значения, текст и значения ошибок в массиве или ссылке игнорируются.

Функция СЛЧИС() возвращает равномерно распределенное случайное число, большее либо равное 0 и меньшее 1. Новое случайное число возвращается при каждом вычислении рабочего листа. Чтобы получить случайное вещественное число между a и b, можно использовать следующую формулу:

=СЛЧИС()*(b-a)+a

Если требуется использовать функцию СЛЧИС для генерации случайного числа, но изменение этого числа при каждом вычислении значения ячейки нежелательно, можно ввести в строку формул =СЛЧИС(), а затем нажать клавишу F9, чтобы заменить формулу на случайное число.

Функция СТЕПЕНЬ(число;степень) возвращает результат возведения числа в степень. Вместо функции СТЕПЕНЬ для возведения в степень можно использовать оператор ^, например 5^2. И число и степень – любые вещественные числа.

Функция СУММ(аргумент1;…;аргумент30) работает также как функция ПРОИЗВЕД, но считает сумму.

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

Диапазон — диапазон вычисляемых ячеек.

Критерий — критерий в форме числа, выражения или текста, определяющего суммируемые ячейки. Например, критерий может быть выражен как 32, "32", ">32", "яблоки".

Диапазон_суммирования — фактические ячейки для суммирования.

Диапазон_суммирования необязательный аргумент, если он пущен, то суммироваться будут значения аргумента диапазон. В этом случае суммироваться будут те ячейки, которые попадают под критерий.

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

Рассмотрим оба варианта работы этой функции на примерах.

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

Рис. 16. Таблица исходных данных

Добавим три строки для вычисляемых значений:

Масса деталей «Корпус»

Масса деталей «Вал»

Масса деталей «Фланец».

Введем в ячейку, соответствующую первой строке функцию:

=СУММЕСЛИ(А2:А14; “Корпус”; В2:В14)

В ячейку, соответствующую второй строке введем функцию:

=СУММЕСЛИ(А2:А14; “Вал”; В2:В14)

В ячейку, соответствующую третьей строке введем функцию:

=СУММЕСЛИ(А2:А14; “Фланец”; В2:В14)

Результат показан на рисунке 17.

В первом случае просуммировались все значения массы, соответствующие детали типа «Корпус», во втором случае – детали типа «Вал» и в третьем случае – детали типа «Фланец».

Рис. 17. Результат работы функции СУММЕСЛИ

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

Тогда формула будет выглядеть следующим образом:

=СУММЕСЛИ(В2:В14;>1)

Особенностью функции является то, что нельзя задавать несколько критериев одновременно, например, нельзя просуммировать значения, попадающие в какой-либо диапазон или соответствующие одному из ряда значений. Но такие функции можно получить путем комбинации функций.

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

Функция СУММКВ(число1; ..;число30) возвращает сумму квадратов аргументов. Аргументами могут быть числа, массивы, имена или ссылки на ячейки, содержащие числа.

Функция СУММКВРАЗН(массив_х;массив_у) возвращает сумму квадратов разностей соответствующих значений в двух массивах.

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

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

Если массив_x и массив_y имеют различное количество элементов, то функция СУММКВРАЗН возвращает значение ошибки #Н/Д.

Уравнение суммы квадратов разностей имеет следующий вид:

где i – текущий номер элемента массива, n – количество элементов массива.

Функция СУММПРОИЗВ(массив1;..;массив30) возвращает сумму произведений соответствующих элементов заданных массивов. СУММПРОИЗВ трактует нечисловые элементы массивов как нулевые.

Функция СУММРАЗНКВ(массив_х;массив_у) возвращает сумму разностей квадратов соответствующих элементов массивов.

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

Если массив_x и массив_y имеют различное количество элементов, то функция СУММРАЗНКВ возвращает значение ошибки #Н/Д.

Уравнение для суммы разностей квадратов имеет следующий вид:

где i – текущий номер элемента массива, n – количество элементов массива.

Функция СУММСУММКВ(массив_х;массив_у) возвращает сумму сумм квадратов элементов.

Уравнение для суммы сумм квадратов имеет следующий вид:

где i – текущий номер элемента массива, n – количество элементов массива.

Функция ФАКТР(число) возвращает факториал аргумента. Аргументом должно быть неотрицательное число, факториал которого вычисляется. Если число не целое, то производится его усечение до целого.

Функция ЦЕЛОЕ(число) округляет аргумент до ближайшего меньшего целого.

Задания

1. Введите в ячейку А1 любое число. Заполните диапазон А1:А10 числами ряда арифметической прогрессии с шагом -4. В столбце В найдите абсолютное значение каждого числа из столбца А, используя протягивание формулы. В столбце С для каждого числа из столбца А найдите значение формулы

,

где А – текущее число из столбца А.

2. Введите в ячейки А1 и А2 любые числа. Заполните диапазон А1:А10 числами ряда арифметической прогрессии. В ячейку В1 поместите формулу, вычисляющую шаг получившейся прогрессии (разность между ячейками А1 и А2). Протяните формулу до ячейки В9 включительно. В столбце С для каждых значений столбцов А и В посчитайте формулу:

,

где А – текущее число из столбца А, В – текущее число из столбца В.

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

4. Введите в ячейку А1 значение угла в 30° (ввести нужно просто число 30). В ячейке В1 посчитайте синус этого угла, учитывая, что функция синус использует в качестве аргумента угол в радианах. Для расчета воспользуйтесь инструментом вложения функций. В ячейке В2 посчитайте косинус этого же угла. В ячейке В3 посчитайте экспоненту от суммы синуса и косинуса 30°, используя ссылки на соответствующие ячейки.

5. Переключите адресацию ячеек на формат R1C1. Введите в ячейки R1C1, R2C1 и R3C1 любые целые числа. В ячейки R1C2, R2C2 и R3C2 введите формулы расчета натурального логарифма, логарифма по основанию 7 и логарифма по основанию 10 соответственно от чисел в соответствующих ячейках столбца 1, используя адресацию в формате R1C1.

Введите в ячейки R3C3, R4C3, R5C3 те же числа, что и в ячейках R1C1 – R3C1 методом копирования. В ячейки R3C4, R4C4, R5C4 введите методом копирования формулы из ячеек R1C2 – R3C2. Проверьте совпадение результатов.

6. Введите матрицу вещественных неположительных чисел в ячейки диапазона В2:D4. Найдите обратную матрицу и разместите ее в ячейках В5:D7. Найдите определитель матрицы В2:D7.

7. Введите матрицу вещественных положительных чисел в ячейки диапазона В2:D4. Найдите обратную матрицу и разместите ее в ячейках В5:D7. Перемножьте матрицы диапазонов В2:D3 и В5:C7.

8. Используя прогрессию заполните числами ячейки А1:А10. В столбце В найдите для каждого числа ближайшее четное целое, в столбце С – ближайшее нечетное целое, в столбце D – ближайшее большее целое, в столбце Е – ближайшее меньшее целое.

9. Используя прогрессию заполните числами ячейки А1:А10. В столбце В найдите для каждого числа ближайшее большее целое с точностью 0,1, в столбце С – ближайшее меньшее целое с точностью 0,01. В столбце D посчитайте разность соответствующих значений столбцов В и С, разделенную на сумму значений столбца А.

10. Используя прогрессию заполните числами ячейки А1:А10. В столбце В найдите для каждого числа ближайшее большее целое, округленное до десятков, в столбце С – ближайшее меньшее целое, округленное до сотен. В ячейке В11 посчитайте сумму значений столбца В, если они равны соответствующим значениям из столбца А.

11. Используя прогрессию заполните числами ячейки А1:А10. В столбце В найдите для каждого числа ближайшее большее целое, округленное до десятков, в столбце С – ближайшее меньшее целое, округленное до сотен. В ячейке С11 посчитайте сумму значений столбца С, если они не равны соответствующим значениям из столбца А.

12. Используя прогрессию заполните числами ячейки А1:А10. В столбце В найдите для каждого числа ближайшее большее целое, округленное до десятков, в столбце С – ближайшее меньшее целое, округленное до сотен. В ячейке В11 посчитайте сумму значений столбца В, если они равны соответствующим значениям из столбца С, а в ячейке С11 посчитайте сумму значений столбца С, соответствующих положительным значениям из столбца А.

13. Заполните диапазон ячеек А1:А10 случайными числами в диапазоне от 1 до 100. Посчитайте произведение всех полученных значений.

14. заполните диапазон ячеек А1:А10 случайными числами в диапазоне от -5 до +5. Посчитайте сумму квадратов полученных чисел в ячейке А11. В столбце В посчитайте отношение соответствующих чисел из столбца А к сумме квадратов, находящейся в ячейке А11.

15. Заполните ячейки А1:А10 случайными числами в диапазоне от -10 до +10. Заполните ячейки В1:В10 числами арифметической прогрессии с начальным значением -100 и шагом 10. В ячейке С1 посчитайте сумму квадратов разностей массивов А1:А10 и В1:В10. В ячейке С2 посчитайте сумму произведений массивов А1:А5 и А6:А10. В ячейке С3 посчитайте сумму разностей квадратов массивов В1:В5 и В6:В10. В ячейке С4 посчитайте квадратный корень от суммы значений в ячейках С1:С3.

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

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

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

Функция ДЛСТР(текст) возвращает длину строки-аргумента или, другими словами, количество символов в строке. Аргументом может быть строка, заключенная в двойные кавычки, например, “Компьютер”, или ссылка на ячейку, содержащую строку, или функцию, возвращаемым значением которой является текст.

Примеры:

=ДЛСТР(“Компьютер”)

Результат: 9

=ДЛСТР(“”)

Результат: 0.

Функция ЗАМЕНИТЬ(стар_текст;нач_поз;число_знаков;нов_текст) заменяет часть строки текста на другую строку.

Аргументы: Стар_текст — текст, в котором требуется заменить некоторые знаки. Нач_поз — позиция знака в тексте стар_текст, начиная с которой знаки заменяются текстом нов_текст. Число_знаков — число знаков в тексте стар_текст, которые заменяются текстом нов_текст. Нов_текст — текст, который заменяет знаки в тексте стар_текст.

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

Пример:

=ЗАМЕНИТЬ(“Физика”;3;3;”.”)

Результат: “Физ.”

=ЗАМЕНИТЬ(“Физика”;3;;”.”)

Результат: “Физ.ика”

=ЗАМЕНИТЬ(“Физика”;3;3;””)

Результат: “Физ”

Функция ЛЕВСИМВ(текст;количество_знаков) возвращает указанное количество знаков с начала строки. Аргумент количество знаков должен быть больше нуля, если этот аргумент опущен, то он считается равным единице.

Пример:

=ЛЕВСИМВ(“Физика”;3)

Результат: “Физ”

=ЛЕВСИМВ(“Физика”)

Результат: “Ф”

Функция ПРАВСИМВ аналогична функции ЛЕВСИМВ, но возвращает указанное количество символов не с начала, а с конца строки.

Функция НАЙТИ(искомый_текст;просматриваемый_текст;нач_поз) находит вхождение одной текстовой строки в другой строке и возвращает начальную позицию первой строки относительно крайнего левого знака второй строки. Аргумент нач_поз необязательный, если он опущен, то поиск осуществляется начиная с первого символа. Прописные и строчные буквы различаются.

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