Использование формул в Excel Матричные функции в Excel

Использование формул в Excel. Матричные функции в Excel.

Формулы в EXCEL Формула – это комбинация постоянных значений, адресов, имён, функций и операторов, которая определяет правила вычисления результата в ячейке. В EXCEL используются следующие символы операций: +, -, *, /, %, ^, &, =, <, >, <=, >=, <>, и символы адресных операций – это двоеточие, точка с запятой и пробел. Двоеточие задаёт диапазон, точка с запятой – объединение диапазонов, а пробел их пересечение. В порядке убывания приоритета операции располагаются в следующем порядке: диапазон- «: » , пересечение – «» , объединение – «; » , отрицание – «-» , процент – «%» , возведение в степень – «^» , умножение и деление – «*» , «/» , сложение и вычитание – «+» , «-» , конкатенация текста – «&» , операции сравнения - =, <, >, <=, >=, <>.

Формулы в EXCEL Когда EXCEL не может вычислить значение по формуле, то выдаётся сообщение об ошибке: ##### - не достаточна ширина ячейки; #ДЕЛ/0! – деление на 0; #Н/Д – неопределённые данные; #ИМЯ? – не распознаётся имя; #ПУСТО! – наличие двух непересекающихся областей; #Число! – проблемы с числом; #ССЫЛ! – неверная ссылка на ячейку; #ЗНАЧ! – значение не того типа.

Матричные функции в Excel При обработке экономической информации, часто приходится работать с массивами. Массивы – это прямоугольные диапазоны значений или формул, которые Excel обрабатывает как единую группу. Результатом обработки массивов, с помощью формул или функций, может быть как массив, так и одно значение. Для решения матричных уравнений чаще всего используются следующие функции: МУМНОЖ(М 1, М 2); МОБР(М); МОПРЕД(М); ТРАНСП(М). Матричные формулы действуют на все ячейки матрицы. Нельзя изменять отдельные ячейки в области, которая определена для матричной операции. Результат появляется в выделенной области после нажатия комбинации клавиш ++.

Матричные функции в Excel Формула с использованием имён может выглядеть так: = МА + МВ

Использование имён в формулах Использование имён упрощает запись формул. Для задания имени нужно выделить область и в меню Вставка открыть подменю Имя, вызвать директиву Присвоить и в открывшемся окне указать имя области. Имя должно начинаться с буквы и содержать не более 255 символов. Не рекомендуется использовать знаки пробел и дефис. Имя не должно совпадать с адресами ячеек. Преимущества использования имён в формулах Excel: – Имена уменьшают вероятность появления ошибок в формулах; – Имена легче запоминаются, чем ссылки на ячейки; – Можно дать имя любой константе или формуле использовать его в других формулах;

Использование имён в формулах Excel (продолжение) - Именованные диапазоны могут сжиматься и растягиваться при вставке и удалении формул; - Имена облегчают перемещение по таблице; - Макросы с именами становятся более универсальными и легче поддаются корректировке; - Имена облегчают ввод ссылок на рабочие листы, находящиеся в других книгах, например: ”=YCONST. XLS !Продажи”; - Один и тот же набор имён может использоваться во всей рабочей книге. Пример: =ЕСЛИ(И(ИТОГ 1>100; ИТОГ 1<1000); ”TRUE”; ”FALSE”)

Макропрограммирование в Excel

Макропрограммирование в Excel Для автоматизации выполнения часто повторяемых действий в Excel используется встроенный язык макропрограммирования. Во всех приложениях Microsoft Office получил распространение VBA(Visual Basic for Applications). Макросы – это программы, написанные на VBA. Существует два способа создания макросов: 1. Запись (протоколирование) действий выполняемых пользователем в Excel. Создавая макрос, макрорекордер Excel записывает все действия пользователя (нажатие клавиш, перемещение курсора, чтение данных, обращение к функциям, присвоение свойств объектам и т. д. ) и интерпретирует их как последовательность команд VBA. Макросы записываются в листы макросов или модули. Модуль может содержать несколько процедур, которые могут обмениваться данными между собой.

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

Макропрограммирование в Excel В качестве примера создадим макрос, позволяющий решить систему из трёх линейных уравнений с тремя неизвестными через обратную матрицу СХ=В (С-1*В=Х).

Макрос

Макрос Все строчки, начинающиеся с символа апостроф, являются комментариями. Каждый оператор в теле макроса состоит из двух частей, разделённых точкой. Первая часть представляет собой объект, указывающий на какую составляющую Excel будет воздействовать оператор. Вторая часть, следующая после точки указывает на действие. Если после описания действия следует знак равенства, действие носит название свойство, а, если знака равенства нет, оно называется командой. VBA осуществляет связь с Excel с использованием команд и свойств объектов. В нашем макросе первый оператор Sub определяет имя процедуры, оператор Range выделяет область для размещения обратной матрицы, следующий с помощью формулы вычисляет и помещает в неё результат. Два последующих работают аналогично. End Sub заканчивает процедуру.

Макрос Созданный макрос можно отредактировать прямо в окне Visual Basic. Увидеть как работают отдельные операторы макроса можно в режиме пошагового выполнения. Для этого надо войти в макрос и используя клавишу F 8 начать его выполнение в пошаговом режиме. Кроме макросов-подпрограмм в модулях можно размещать функции, написанные на VBA и используемые на рабочем листе. Текст такой функции можно запрограммировать с помощью редактора Visual Basic. После того как текст функции размещён в модуле, она становится доступной через диалоговое окно Мастера функций в категории функции определённые пользователем.

Проектирование экранных форм Создание Windows-приложений на VBA включает в себя не только составление алгоритмов и написание программ, но и проектирование окон (экранных форм) будущего приложения, размещения в этом окне необходимых элементов управления – кнопок, текстовых полей и т. д. Поэтому вместо понятия программирования, которое подразумевает только написание программы на VBA, говорят о создании программного проекта. Экранная форма – это графическое представление окна Windows-приложения вместе с содержанием этого окна. Содержание включает в себя: • Совокупность свойств этого окна с их значениями; • Совокупность объектов, находящихся в этом окне; • Совокупность свойств этих объектов, а также их значений.

Проектирование экранных форм Программный проект – это совокупность частей, составляющих будущее Windows-приложение. Любой проект должен состоять из одной или нескольких экранных форм и одного или нескольких программных модулей. Проект хранится в файле с расширением. vbr. Программный код проекта привязан к отдельным элементам (объектам) экранной формы. В процессе решения задачи мы получаем сигналы, в VBA они называются событиями. Например, щелчок мыши по какому-либо объекту экранной формы, загрузка новой экранной формы и т. д. Большинство процедур, из которых состоит программный код VBA, привязаны к событиям, они называется процедурами-событиями.

Проектирование экранных форм При создании нового проекта в окне конструктора форм сразу появляется пустая форма с именем Form 1. Если форма является выделенным объектом, то окно свойств отображает её свойства и эти свойства можно просмотреть и изменить. Так для изменения заголовка следует найти свойство Caption, выделить его двойным щелчком мыши и ввести текст заголовка. Свойство Back. Color открывает палитру цветов для изменения цвета формы. Свойства Left, Top определяют положение формы на экране при запуске программы на выполнение. Это координаты левого верхнего угла формы в твипах (1/1440 логического, т. е. зависящего от разрешающей способности монитора, дюйма). Для изменения можно ввести новые значения с клавиатуры или мышью переместить границу изображения формы на экране. В режиме выполнения можно перемещать форму, как

Рассмотрим пример проектирования Windowsприложения. Пусть требуется определить площадь стен комнаты, имеющей форму прямоугольной призмы. Содержание экранной формы представлено на следующем слайде. Начнём с установки имени (идентификатора) формы, надписи в строке заголовка формы и цвета фона. Эти свойства устанавливаются в строках: Name, Caption, Back. Color. Значения первых двух из этих свойств устанавливаем: FПлощадь. Стен и Площадь стен комнаты, а значение последнего выбираем с помощью раскрывающейся панели. Выбираем квадратик с белым цветом и значением свойства Back. Color экранной формы станет белый цвет. Далее в соответствии с макетом размещаем на экранной форме шесть объектов Метка, которые будут содержать надписи. Для этого щёлкнем по пиктограмме инструмента в виде заглавной буквы «А» , поместим указатель мыши в то место экранной формы, где будет находиться левый верхний угол будущего объекта и, нажав левую кнопку мыши, протащим указатель в то место, где будет находиться правый нижний угол объекта. Так же создадим остальные пять

Пример С помощью инструмента Текстовое поле разместим на экранной форме четыре поля, в которые будут заноситься исходные значения и результат вычислений. И, наконец, с помощью соответствующих инструментов поместим на экранную форму объекты Командная кнопка и Изображение. После формирования макета необходимо задать значения свойств всех объектов формы. Для объектов Метка наиболее важным является свойство Caption – текст надписи. Зададим эти значения, а также установим тип, размер и цвет шрифта в соответствующих полях Окна свойств объекта. Для объектов Текстовое поле свойству Name зададим соответственно значения: Длина, Ширина, Высота, Площадь. Для объекта Изображение определим свойство Picture, связав его с файлом, хранящим изображение.

Пример Теперь перейдём к объекту Командная кнопка. Сначала зададим значения свойств Name и Caption. Далее необходимо связать с этим объектом программный код. Для этого раскроем окно программного кода: команда: Code в меню View. В верхней части этого окна есть две строки раскрывающихся списков: список созданных объектов и список процедур, который пока пуст. Раскрыв список объектов щёлкнем по объекту Командная. Кнопка, затем раскроем правый список и в окне появится заготовка будущей процедуры: Private Sub Командная. Кнопка_Clicke() End Sub

Теперь наберём текст процедуры: Private Sub Командная. Кнопка_Clicke() A = Val(Длина. Text) B= Val(Ширина. Text) H = Val(Высота. Text) S = 2 * (A + B) * H Площадь. Text = Str(S) End Sub Функция Val(Х) преобразует строку символов в число, а функция Str(Х) производит обратное преобразование. Чтобы запустить программу можно воспользоваться командой Run Главной панели проекта, клавишей Старт линейки инструментов или клавишей F 5 клавиатуры. Теперь, поместив в первые три текстовых поля значения Длины, Ширины и Высоты, нажмём командную кнопку и получим вычисленное по формуле значение S в последнем текстовом поле.