Если опустить знак равенства в записи сумм сз с5 excel воспримет ваш ввод как

Обновлено: 08.07.2024

Этим видео-уроком я хочу начать серию видео-уроков, которые будут посвящены основам работы в программе Microsoft Word.

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

Таблица 3. Операторы, используемые в формулах

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

Ввод формул начинается с выделения ячейки, в которую и будет вставлена формула. При вводе простых формул (без обращения к Мастеру функций) сначала вводится знак =, который отобразится в активной ячейке (и в Строке формул). Одновременно в активной ячейке появится курсор, приглашающий вводить вычисляемое выражение. Чтобы воспользоваться для ввода формулы Мастером функций, достаточно щелкнуть в Строке формул по кнопке тогда одновременно с появлением в активной ячейке (и в Строке формул) знака = откроется диалоговое окно Мастера функций (рис. 26):

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

В окне Мастера функций в раскрывающемся списке Категория: нужно выбрать соответствующую категорию функций, а затем из списка в окне Выберите функцию: – выбрать необходимую функцию.
Если аргументом функции является ссылка на ячейку или диапазон ячеек (возможно, на другом листе книги), то для ввода достаточно выделить соответствующую ячейку или диапазон ячеек. Чтобы при этом окно диалога не загораживало лист книги, его можно убрать, щелкнув по кнопке справа от поля ввода, оставив видимой только эту строку. Для восстановления окна диалога после ввода аргумента нужно щелкнуть там же по кнопке . Аргументом функции может быть также выражение, в том числе обращение к функции или содержащее обращения к функциям. Для их ввода можно вновь воспользоваться Мастером функций.

В диалоговом окне Мастера функций имеется гипертекстовая ссылка Справка по этой функции для получения подробной информации о назначении и работе с выделенной функцией.
Особое место среди функций занимает функция СУММ, которую можно вызвать с помощью специальной кнопка Автосумма При щелчке на ней сразу вставляется функция суммы для ближайшего диапазона заполненных ячеек в качестве аргумента. Аргумент можно скорректировать, выделив нужный диапазон ячеек.
В качестве примера использования функций рассмотрим формулы, вычисляющие корни квадратного трехчлена: ax2+bx+c=0. Они введены в ячейки A2 и A3 и имеют следующий вид:

В ячейках A1, B1 и C1 находятся значения коэффициентов a, b и с, соответственно. Если вы ввели значения коэффициентов a=1, b=-5 и с=6 (это означает, что в ячейках A1, B1 и C1 записаны числа 1, 5 и -6), то в ячейках A2 и A3, где записаны формулы, вы получите числа 2 и 3. Если вы измените число в ячейке A1 на -1, то в ячейках с формулами вы получите числа -6 и 1.

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

Таблица 4. Расшифровка кодов ошибок

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

Абсолютные, относительные и смешанные ссылки на ячейки
Ссылка есть указание в формуле адреса ячейки, содержимое которой должно быть использовано при вычислении формулы. Существуют три типа ссылок на ячейки: относительные, абсолютные и смешанные. Относительная ссылка - это ссылка, состоящая из имени столбца и имени строки, например, B5. Абсолютная ссылка - это ссылка, перед именами столбца и строки которой стоит символ $, например, $B$5. Имена ячеек и диапазонов в формулах также представляют абсолютные ссылки на них. Различие между относительной и абсолютной ссылками проявляется при копировании (распространении с помощью маркера заполнения) формулы в другую ячейку. В формуле-копии аргумент, заданный абсолютной ссылкой, не изменится, а у аргумента, заданного относительной ссылкой, номер строки (столбца) изменится на величину разности номеров строк (столбцов) ячеек, содержащих копию формулы и оригинал.

Например, если скопировать формулу =СУММ(A1:A3) из ячейки A4 в ячейку B5, то копия будет иметь вид =СУММ(B2:B4).
Допускаются и смешанные ссылки, когда одна из составляющих задана как абсолютная, со знаком $ (например, A$4 или $A4); она не изменяется при копировании и распространении формулы. Быстро заменить один из вариантов ссылки на любой другой (абсолютный, относительный, смешанный) можно последовательным нажатием клавиши F4, предварительно разместив рядом с ней в формуле курсор ввода.
При перемещении формулы в новое место таблицы ссылки в формуле не изменяются.

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

Стоит отметить одну важную особенность: признаком того, что можно редактировать данные ячейки, является мигающий в ней текстовый курсор. Курсор появляется при выполнении любого из нижеперечисленных действий. За исключением последнего указанного с списке случая, если курсор не появился, то значит вы что-то сделали не так.

Двойной щелчок на ячейке

Пожалуй, это самый распространённый способ отредактировать содержимое любой ячейки Excel. Способ подходит как для ввода данных в пустую ячейку, так и для редактирования существующего содержимого ячейки. Если вам нужно что-то ввести в ячейку или изменить в ней (текст, числа, формулы), то просто выполните двойной щелчок.

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

Клавиша F2 на клавиатуре

Про данный способ отредактировать содержимое ячейки листа Excel почему-то мало кто знает. Мой опыт проведения различных учебных курсов показывает, что это, прежде всего, связано с недостаточным знанием Windows. Дело в том, что наиболее распространённой функцией клавиши F2 в Windows является начало редактирования чего-либо. Excel тут не исключение. Данный способ работает независимо от того, есть данные в ячейке или нет.

Также вы можете использовать нажатие F2 для редактирования имён файлов и папок в Проводнике Windows — попробуйте и убедитесь сами, что способ достаточно универсален (файл или папка должны быть выделены).

Клавиша Backspace на клавиатуре

Хорошо подходит для случая, когда в ячейке уже есть данные, но их нужно удалить и ввести новые. Нажатие Backspace (не путать с Esc!) приводит к стиранию имеющихся в ячейке данных и появлению текстового курсора. Если вам нужно просто стереть данные ячейки, но вводить новые не требуется, то лучше нажать Delete.

Если у вас выделена группа ячеек, то нажатие Backspace приведёт к стиранию содержимого только левой верхней ячейки выделенного диапазона. Для удаления содержимого группы ячеек также лучше использовать Delete.

Редактирование в строке формул

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

Если нужно написать много текста или большую и сложную формулу, то строку формул можно расширить. Для этого есть специальная кнопка, показанная на рисунке ниже. Также не забывайте, что перенос строк в Excel делается через сочетание Alt + Enter.

Щёлкнуть на ячейке и начать писать

Самый простой способ. Лучше всего подходит для ввода данных в пустую ячейку — выделите ячейку щелчком и начните вводить данные. Как только вы нажмёте первый символ на клавиатуре, содержимое ячейки очиститься (если там что-то было), а в самой ячейке появится текстовый курсор. Будьте внимательны — таким образом можно случайно(!) стереть нужные вам данные, нажав что-то на клавиатуре!

Подтверждение ввода данных в ячейку

Проводя курсы Excel, я наблюдал очень(!) распространённую проблему у начинающих пользователей Excel — многие, написав в ячейку данные, забывают подтвердить ввод. Обязательно подтверждайте ввод данных в ячейку одним из ниже указанных способов!

После подтверждения ввода данных, текстовый курсор в ячейке пропадает. Если курсор не пропал, значит ввод данных не подтверждён!

Клавиша Enter

Универсальный и самый распространённый способ подтверждения ввода данных куда-либо. Нажатие Enter завершает ввод данных в ячейку в любом случае, независимо от того, пишете ли вы там формулу или простой текст. Выделение переходит на ячейку ниже (если не настроить иное, чего обычно никто никогда не делает).

Подсчет количества определенного значения ячейки в Excel при условии

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

Вид исходной таблицы:

Предварительно выделим ячейки E2:E5, введем приведенную ниже формулу:

B3:B19 – диапазон ячеек с оценками за экзамен;
D2:D5 – диапазон ячеек, содержащих критерии для подсчета числа совпадений.

В результате получим таблицу:

Статистический анализ посещаемости с помощью функции СЧЁТЕСЛИ в Excel

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

Вид исходной таблицы:

Поскольку каждый пользователь имеет свой уникальный идентификатор в базе данных (Id), выполним расчет числа пользователей сайта за день по следующей формуле массива и для ее вычислений нажмем комбинацию клавиш Ctrl+Shift+Enter:

Выражение 1/СЧЁТЕСЛИ(A3:A20;A3:A20) возвращает массив дробных чисел 1/количество_вхождений, например, для пользователя с ником sam это значение равно 0,25 (4 вхождения). Общая сумма таких значений, вычисляемая функцией СУММ, соответствует количеству уникальных вхождений, то есть, числу пользователей на сайте. Полученное значение:

Для определения количества просмотренных страниц пользователями default и user_1 запишем формулу:

В результате расчета получим:

Ссылки на другой лист

Иногда в формуле необходимо указать ссылку на данные из другого листа. Делается это довольно просто:

На Листе 2 введены следующие значения.

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

Относительные ссылки

Чтобы понять, что же такое относительные ссылки, рассмотрим следующий пример.

У нас есть таблица продаж за первый квартал 2019 года. Воспользуемся функцией СУММ и подсчитает общую сумму продаж за январь месяц. Формула будет выглядеть так:

=СУММ(B3:B6)

Далее скопируем данную формулу в ячейку C7.

При копировании исходной формулы Эксель в ячейку С7 программа немного изменяет формулу, после чего она приобретает такой вид:

=СУММ(СЗ:С6)

Excel изменяет указатель столбца с В на С, поскольку копирование проводилось слева направо по строкам.

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

=CУMM(B3:D3)

При копировании этой формулы Эксель в ячейку Е4 программа создает следующую формулу:

=СУММ(В4:D4)

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

Абсолютные ссылки

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

Одним из самых распространенных исключений является сравнение ячеек некоторого диапазона с одним значением. Например, вам может потребоваться указать в ячейках объем продаж каждого из подразделений относительно общего объема продаж компании в целом. На рабочем листе объемов продаж компании “Наш концерн” такая ситуация возникает при копировании формулы Эксель, вычисляющей, какой процент составляют ежемесячные объемы (ячейки B9:D9) в ежеквартальном объеме продаж (ячейка Е7).

Предположим, что мы начинаем ввод этих формул в строке 9 с ячейки В9. Формула в этой ячейке вычисляет процент продаж в январе (В7) относительно квартального (Е7) методом деления. Что может быть проще?

=В7/Е7

Эта формула делит итог январских продаж (в ячейке В7) на квартальный итог в ячейке Е7. А теперь посмотрите, что произойдет, если перетащить маркер заполнения на одну ячейку вправо, чтобы скопировать формулу в ячейку С9:

=C7/F7

Чтобы предотвратить изменение ссылки на ячейку во всех создаваемых копиях формулы Эксель, нужно преобразовать ссылку из относительной в абсолютную. Это выполняется с помощью клавиши <F4> после переключения Excel в режим редактирования (с помощью клавиши <F2>). В ответ на это программа помещает перед буквой столбца и номером строки в формуле знаки доллара. В качестве примера рассмотрим скриншот ниже. Ячейка В9 на этом рисунке содержит корректную формулу, которую уже можно копировать в диапазон ячеек C9:D9:

=B7/$E$7

Посмотрим теперь на эту формулу в ячейке С9 после копирования в диапазон C9:D9 методом перетаскивания. В строке формул отображается следующее:

=С7/$Е$7

Поскольку ссылку Е7 в исходной формуле мы заменили ссылкой $Е$7, все ее копии будут иметь те же абсолютные (т.е. неизменные) значения.

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

Дважды щелкните на ячейке с формулой или нажмите клавишу <F2>, чтобы приступить к редактированию.
Переместите точку вставки к ссылке, которую хотите преобразовать в абсолютную.
Нажмите клавишу <F4>.
Когда закончите редактирование, щелкните на кнопке Ввод в строке формул, а затем скопируйте ее в диапазон ячеек путем перетаскивания маркера заполнения.

Нажимайте клавишу <F4> только тогда, когда необходимо преобразовать ссылку на ячейку в полностью абсолютную. Если нажмете клавишу <F4> второй раз, то получите так называемую смешанную ссылку, в которой строка абсолютна, а столбец относителен (например, Е$7). Если нажмете клавишу <F4> еще раз, то получите другой тип смешанной ссылки, в которой столбец абсолютен, а строка относительна (например, $Е7). Если же нажать клавишу <F4> еще раз, ссылка станет полностью относительной (например, Е12). Таким образом, вы вернетесь к тому, с чего начали. Последующие нажатия клавиши <F4> повторят вышеописанный цикл преобразований.

Если программа Excel установлена на устройстве с сенсорным экраном, к которому не подключена физическая клавиатура, то единственный способ преобразования адресов ячеек в формулах из относительной формы в абсолютную либо смешанную — открыть экранную (виртуальную) клавиатуру. С ее помощью добавьте значки доллара перед буквой столбца и/или номером строки для соответствующего адреса ячейки в строке формул.

Формулы Эксель: Использование функций

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

Функцией называют предопределенную формулу, выполняющую определенный тип вычислений. Ей необходимо передать значения, используемые в операции (они называются аргументами). Как и в простых формулах, аргументами функций могут быть числа (например, 22 или -4,56), а также ссылки на ячейки (В10) или диапазоны ячеек (СЗ: РЗ).

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

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

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

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

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

Вставка функции в формулу с помощью мастера

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

Диалоговое окно мастера функций содержит текстовое поле Поиск функции (Search for a Function), а также списки Категория (Or Select a Category) и Выберите функцию (Select a Function). Когда открывается окно вставки функции, автоматически выбирается категория десяти недавно использованных функций.

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

Чтобы получить подробную справку по выбранной функции, щелкните на ссылке Справка по этой функции, находящейся в левой нижней части окна.

Если нужной функции не оказалось в списке недавно использованных, выберите соответствующую категорию. Если не можете определиться с категорией, то поищите функцию, введя ее описание в поле Поиск функции, а затем нажмите клавишу <Enter> или щелкните на кнопке Найти. Excel откроет список рекомендуемых функций, и вы сможете выбрать любую из них.

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

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

В качестве примера выберите функцию СУММ (она обычно лидирует в категории часто используемых) и щелкните на кнопке ОК. Как только вы это сделаете, программа вставит в текущую ячейку и строку формул запись СУММ (). Затем откроется диалоговое окно ввода аргументов. В этом окне следует указать аргументы функции.

Функция СУММ может суммировать до 255 аргументов. Совершенно очевидно, что все они находятся далеко не в одной ячейке. На практике вам придется чаще всего суммировать значения, содержащиеся в соседних ячейках.

Для того чтобы выбрать первый аргумент функции, щелкните на ячейке рабочего листа или перетащите указатель мыши по диапазону ячеек. В текстовом поле Число1 (Numberl) программа отобразит адрес ячейки (или диапазон адресов), а в нижней части окна, в поле Значение (Formula result), появится результат вычислений.

Имейте в виду, что во время выбора ячеек на рабочем листе диалоговое окно аргументов можно свернуть; при этом отображаться будет только поле Число! Чтобы свернуть окно аргументов, щелкните на кнопке, расположенной справа от поля Число1. После этого можно выделить диапазон ячеек и щелкнуть на кнопке восстановления окна (в свернутом окне эта кнопка будет единственной) или нажать клавишу <Esc>. Вместо свертывания можете переместить это окно в любое свободное место экрана.

Если на рабочем листе заполнено множество ячеек, щелкните на поле Число2 или нажмите клавишу <Tab>. (Excel отреагирует на это, открыв поле Число3.) В поле Число2 введите аналогичным образом второй диапазон ячеек, только на этот раз для сворачивания окна щелкайте на кнопке рядом с этим полем. В поле результата вычислений появится сумма уже двух диапазонов значений. При желании можете выделить несколько таких диапазонов (Число2, Число3, Число4 и т.д.).

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

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

Автоматическая вставка знака равенства в ячейки с формулой

Вы можете сделать следующее, чтобы автоматически вставить знак равенства в ячейки с формулой.

1. Выберите пустую ячейку (например, B1) для поиска результата, введите формулу = " jch-lazyload" svg+xml;base64,PHN2ZyB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciIHdpZHRoPSIxIiBoZWlnaHQ9IjEiPjwvc3ZnPg==" data-src="https://cdn.extendoffice.com/images/stories/doc-excel/doc-auto-insert-equal-sign/doc-add-equal-sign-2.jpg" alt="">

2. Продолжая выбирать ячейку B1, перетащите маркер заполнения к ячейкам, в которые вы хотите добавить знаки равенства.

3. Выберите столбец со знаками равенства и удерживайте Ctrl + C чтобы скопировать их, затем щелкните Главная > макаронные изделия > Ценности. Смотрите скриншот:

4. Нажмите Ctrl + F клавиши одновременно, чтобы открыть Найти и заменить диалоговое окно. В диалоговом окне щелкните значок Заменять вкладку и введите знак равенства отдельно в поле Найти то, что и Заменить поля, затем щелкните Заменить все кнопка. Смотрите скриншот:

5. Затем Microsoft Excel появится диалоговое окно, нажмите OK кнопка. И вы можете видеть, что формулы активированы.

Автоматическая вставка знака равенства в ячейки с помощью Kutools for Excel

Описанный выше метод является многоэтапным и трудоемким. Здесь мы представляем вам Добавить текст полезности Kutools for Excel. С помощью этой утилиты вы можете легко добавить знак равенства ко всем выбранным ячейкам одновременно.

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

1. Выберите диапазон с ячейками, в которые вы хотите добавить знаки равенства. А затем нажмите Kutools > Текст > Добавить текст.

2. в Добавить текст диалоговом окне введите знак равенства в Текст выберите Перед первым персонажем вариант в Позиция раздел и, наконец, щелкните OK кнопка. Смотрите скриншот:

Затем в выбранные ячейки сразу же вставляются знаки равенства, и формулы активируются.

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

Microsoft Excel – самая популярная в мире программа электронных таблиц, входящая в стандартный офисный пакет Microsoft Office. Она выдержала уже несколько переизданий, возможности каждой новой версии расширяются, появляются новые формулы, макросы для вычислений, которые используются в бухгалтерском учете, научных и инженерных приложениях. MS Excel – универсальная программа для составления электронных таблиц любой сложности и дизайна, графиков и диаграмм, поэтому современные офисные работники готовят в ней отчеты, экспортируют в Excel данные из других офисных программ.

Приложение Excel, будучи составной частью популярного пакета (имеется в виду Microsoft Office), по массовости своего использования уступает, пожалуй, только другому приложению этого же пакета (имеется в виду редактор Word). Хотя это утверждение основано и не на статистических данных, однако, думается, выглядит вполне правдоподобно. В любом случае, малознакомым широкому кругу пользователей приложение Excel никак не назовешь. У Microsoft Excel есть существенный, и, как представляется, не до конца раскрытый потенциал, и задача данного пособия состоит в том, чтобы показать возможности MS Excel при решении прикладных задач.

Основные функции Excel:

− проведение различных вычислений с использованием мощного аппарата функций и формул;

− исследование влияния различных факторов на данные; − решение задач оптимизации;

− получение выборки данных, удовлетворяющих определенным критериям;

− построение графиков и диаграмм.

Общие сведения о книгах и листах Microsoft Excel

При запуске Excel открывается рабочая книга с пустыми рабочими листами. Рабочая книга в Microsoft Excel представляет собой файл, используемый для обработки и хранения данных. Такие файлы имеют расширение - .xlsх. Каждая книга может состоять из нескольких листов, поэтому в одном файле можно поместить разнообразные сведения и установить между ними необходимые связи. Имена листов отображаются на ярлычках в нижней части окна книги. Для перехода с одного листа на другой следует указать соответствующий ярлычок. Название активного листа выделено жирным шрифтом. Рабочий лист - это собственно электронная таблица, основной тип документа, используемый в Excel для хранения и манипулирования данными. Он состоит из ячеек, организованных в столбцы и строки, и всегда является частью рабочей книги. В рабочем листе Excel 2007имеется 16 384 столбца, заголовки которых указаны в верхней части листа с помощью букв английского алфавита и 1048576 строк. Столбцы по умолчанию озаглавлены буквами, строки - цифрами. Щелкните мышью на любой ячейке вашего рабочего листа и, таким образом, вы сделаете ее текущей или активной (она пометится рамкой). В поле имени, будет показан адрес текущей ячейки, например В4. Ячейка - это основной элемент электронной таблицы только в ней может содержаться какая-либо информация (текст, значения, формулы).

Элементы экрана

Строка заголовка находится в верхней части экрана и имеет обычный вид для программ, работающих под управлением Windows, дополнительно здесь установлена кнопка Office , которая предназначена для вывода списка возможных действий с документом, включая открытие, сохранение и печать. Также на строке заголовка есть панель быстрого доступа.

Рис. 1.1. Структура рабочего листа

Строка меню.

Под строкой заголовка находится строка меню, в которой перечислены группы команд: Главная, Вставка, Разметка страницы, Формулы, Данные, Рецензирование, Вид. Каждая группа объединяет набор команд, имеющих общую функциональную направленность.

Рис. 1.2. Вид строки меню

Ввод и редактирование данных

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

Строка формул Microsoft Excel, используется для ввода или редактирования значений или формул в ячейках или диаграммах. Здесь выводится постоянное значение или формула активной ячейки. Для ввода данных выделите ячейку, введите данные и щелкните по кнопке с зеленой «галочкой» или нажмите ENTER. Данные появляются в строке формул по мере их набора.

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

Рис. 1.3. Вид строки формул

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

Каждая ячейка имеет адрес, который обозначается именем столбца и именем строки. Например А2. Если надо задать адрес ячейки, которая находится на другом рабочем листе или в другой рабочей книге, перед именем ячейки задают имя рабочего листа, а во втором случае и имя рабочей книги. Например: Лист 1!А1 или =[Работа1]Задание1!$B$4 .

Наряду с понятием адреса ячейки в электронной таблице используется понятие ссылки. Ссылка– это элемент формулы, который используется тогда, когда надо сослаться на какую-нибудь ячейку таблицы. В этом случае адрес будет использоваться в качестве ссылки.

Есть два стиля представления ссылок в Microsoft Excel:

- Стиль ссылок R1C1 (здесь R — row (строка), C — column (столбец)).

Ссылки в Excel бывают 3-х видов:

- Относительные ссылки (пример:A1);

- Абсолютные ссылки (пример: $A$1);

- Смешанные ссылки (пример: $A1 или A$1, они наполовину относительные, наполовину абсолютные).

Относительные ссылки

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

Например, ячейка B2 содержит формулу = B5+C8, т. е. первый операнд находится на три ячейки ниже в том же столбце, а второй операнд находится на 6 строк ниже и один столбец правее ячейки B2. При копировании данной формулы и вставке ее в ячейку С3, ссылки в ней снова будет указывать на ячейки, расположенные: первая - на три ячейки ниже в том же столбце, вторая - на 6 строк ниже и один столбец правее ячейки С3. Так, если формула из ячейки B2 копируется в ячейку С3, то формула примет вид =С6 + D9, а если ско-пировать содержимое В2 в B3, то в ячейке B3 формула примет вид = B6+C9.

Рис. 1.4. Относительная ссылка

Абсолютные ссылки

Если необходимо, чтобы ссылки не изменялись при копировании формулы в другую ячейку, используют абсолютные ссылки. Абсолютная ссылка всегда указывает на одну и ту же ячейку, независимо от расположения формулы, её содержащей. Для создания абсолютной ссылки на ячейку необходимо поставить знак доллара ($) перед той частью ссылки, которая не должна изменяться. Например, если в A1 находится формула =$B$5+$C$8, то при копировании содержимого ячейки A1 в ячейку В2 или A3 в этих ячейках также будетнаходиться формула =$B$5+$C$8, что говорит о том, что исходные данные всегда будут браться из ячеек В5 и С8.

Рис. 1.5. Абсолютная ссылка

Смешанные ссылки

Ссылки на ячейки могут быть смешанными. Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов имеет вид $A1 или $B1. Абсолютная ссылка строки имеет вид A$1, B$1. При изменении позиции ячейки, содержащей формулу, относительная часть ссылки изменяется, а абсолютная не изменяется. При копировании формулы вдоль строк и вдоль столбцов относительная часть ссылки автоматически корректируется, а абсолютная остается без изменений.

Рис. 1.6. Смешанная ссылка

Кроме понятия ячейки используется понятие диапазона – прямоугольной области, состоящей из нескольких (или одного) столбцов и нескольких (или одной) строк. В качестве адреса диапазона указываются адреса левой верхней и правой нижней ячеек диапазона, разделенные знаком двоеточие ( : ). Например, диапазон A1:C4 содержит 12 ячеек (по 3 ячейки в строках и 4 ячейки в столбцах).

Для работы с несколькими ячейками сразу необходимо выделить блок ячеек. Это выполняется следующим образом: для смежных ячеек щелкните на ячейке и удерживая кнопку мыши, протяните по листу указателем. При этом будет произведено выделение всех смежных ячеек. Блок описывается двумя адресами, разделенными знаком двоеточия - адресом верхней-левой и нижней-правой ячеек. На рисунке, например, выделен блок: A2:D4.

Рис. 1.7. Диапазон ячеек

В Excel можно выделять целые рабочие листы или их части, в том числе столбцы, строки и диапазоны (группы смежных или несмежных ячеек). Для выделения несмежных строк, столбцов или диапазонов необходимо нажать и удерживать в процессе выделения клавишу Ctrl.

Автозаполнение

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

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

Однако, если ячейка содержит число, дату или период времени, то при копировании с помощью средства Автозаполнение происходит приращение значения её содержимого. Например, если ячейка имеет значение «Январь», то существует возможность быстрого заполнения других ячеек строки или столбца значениями «Февраль», «Март» и так далее. Могут создаваться пользовательские списки автозаполнения для часто используемых значений, например, названий районов города или списка фамилий студентов группы.

Рис. 1.8. Пример автозаполнения по месяцам

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

Для создания рядов данных необходимо выполнить следующие действия:

- введите в ячейку первый член ряда;

- подведите указатель мыши к черной точке в правом нижнем углу выделенной ячейки (в этот момент белый крестик переходит в черный) и нажмите на левую кнопку мыши;

- удерживая нажатой кнопку мыши, выделите нужную часть строки или столбца;

- после того как вы отпустите кнопку мыши, выделенная область заполнится данными.

Понятие формулы

Формулы – это выражение, начинающееся со знака равенства «═» и состоящее из числовых величин, адресов ячеек, функций, имен, которые соединены знаками арифметических операций. К знакам арифметических операций, которые используются в Excelотносятся:сложение; вычитание; умножение; деление; возведение в степень.

Некоторые операции в формуле имеют более высокий приоритет и выполняются в такой последовательности:

возведение в степень и выражения в скобках;

умножение и деление;

сложение и вычитание.

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

В формуле может быть указана ссылка на ячейку, если необходимо в расчетах использовать её содержимое. Поэтому ячейка, содержащая формулу, называется «зависимой ячейкой», а ячейка содержащая данное – «влияющей ячейкой». При создании на листе формул можно получить подсказку о том, как связаны зависимые и влияющие ячейки. Для поиска таких ячеек служат команды панели инструментов «Зависимости». Значение зависимой ячейки изменится автоматически, если изменяется значение влияющей ячейки, на которую в формуле есть ссылка. Формулы могут ссылаться на ячейки или на диапазоны ячеек, а также на их имена или заголовки.

Перемещение и копирование формул

Ячейки с формулами можно перемещать и копировать. При перемещении формулы все ссылки (и абсолютные и относительные ), расположенные внутри формулы, не изменяются. При копировании формулы абсолютные ссылки не изменяются, а относительные ссылки изменяются согласно новому расположению ячейки с формулой.

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

Рис.1. 9. Пример автозаполнения формул

Таблица 1

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

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

Еxcel не может распознать текст, введённый в формулу, например неверное имя функции

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

Неверная ссылка на ячейку

Невозможно вычислить результат формулы, либо он слишком велик или мал для корректного отображения в ячейки

Результат поиска пересечений двух непересекающихся областей, то есть неверная ссылка

Функции Excel

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

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

Синтаксис функций

Функции состоят из двух частей: имени функции и одного или нескольких аргументов. Имя функции, например СУММ, - описывает операцию, которую эта функция выполняет. Аргументы задают значения или ячейки, используемые функцией. В формуле, приведенной ниже: СУММ - имя функции; В1:В5 - аргумент. Данная формула суммирует числа в ячейках В1, В2, В3, В4, В5.

Знак равенства в начале формулы означает, что введена именно формула, а не текст. Если знак равенства будет отсутствовать, то Excel воспримет ввод просто как текст.

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

Читайте также: