Советы и рекомендации по работе с формулами. Работа в экселе с формулами и таблицами для начинающих

Создание простых формул в Microsoft Excel

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

Создаем первую простую формулу в Excel

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

Чтобы создать формулу, выделите ячейку, которая будет ее содержать. В нашем примере мы выделили ячейку B3.
Введите знак равенства (=). Обратите внимание, что он появляется как в самой ячейке, так и в строке формул.
Введите адрес ячейки, которая должна стоять первой в формуле. В нашем случае это ячейка B1. Её границы будут выделены синим цветом.
Введите математический оператор, который хотите использовать. В нашем примере мы введем знак сложения (+).
Введите адрес ячейки, которая должна стоять второй в формуле. В нашем случае это ячейка B2. Её границы будут выделены синим цветом.
Нажмите Enter на клавиатуре. Формула будет создана и рассчитана.

Если результат вычислений не поместится в ячейку, то вместо ожидаемого результата могут появиться знаки решетки (#######). Это означает, что ширины столбца недостаточно для отображения всего содержимого. Просто увеличьте ширину столбца для отображения данных.

Основное преимущество формул со ссылками

Главное преимущество ссылок в том, что они позволяют вносить изменения в данные на листе Excel без необходимости переписывать сами формулы. В следующем примере мы изменим значение ячейки B1 с $1200 на $1800. Формула будет автоматически пересчитана и отобразится новое значение.

Создание простых формул в Excel

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

Создаем формулу в Excel, выбирая ячейку мышкой

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

Выделите ячейку, которая будет содержать формулу. В нашем примере мы выделили ячейку D3.
Введите знак равенства (=).
Выделите ячейку, которая должна стоять первой в формуле. В нашем случае это ячейка B3. Адрес ячейки появится в формуле, а вокруг нее отобразится синяя пунктирная линия.
Введите математический оператор, который хотите использовать. В нашем примере это знак умножения (*).
Выделите ячейку, которая должна стоять второй в формуле. В нашем случае это ячейка C3. Адрес ячейки появится в формуле, а вокруг нее отобразится красная пунктирная линия.
Нажмите Enter на клавиатуре. Формула будет создана и рассчитана.

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

Создание простых формул в Excel

Оцените качество статьи. Нам важно ваше мнение:

office-guru.ru

Советы и рекомендации по работе с формулами

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .

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

В этой статье

Советы и рекомендации по созданию формул

Правила, чтобы избежать распространенных ошибок, что можно сделать при создании формул

Правила, позволяющие избежать распространенных ошибок при создании формул

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

Правила	Действия
Согласовывайте все открывающие и закрывающие скобки	Убедитесь, что каждой скобке в формуле соответствует открывающая или закрывающая скобка. При создании формулы в Excel вводимые скобки выделяются цветом.
Используйте двоеточие для указания диапазона	Для создания ссылки на диапазон ячеек в формуле используйте двоеточие (:) между первой и последней ячейкой диапазона, а не другие символы, такие как дефис (-). Например, вводите A1:A5 вместо A1-A5. Двоеточие не используется как математический оператор в формулах, а дефис выполняет функцию знака минус.
Указаны обязательные аргументы	Чтобы правильно ввести формулу, укажите все обязательные аргументы. Необязательные аргументы в синтаксисе формулы заключены в квадратные скобки. Вводите их по мере необходимости.
Не вкладывайте более 64 функций	При вложении других функций в аргументы формулы, в которой используется функция, не превышайте ограничение в 64 уровня вложенных функций этой формулы.
Заключайте имена других листов в апострофы	Если формула ссылается на значения или ячейки других листов или книг, имена которых содержат небуквенные символы, такие имена следует заключать в апострофы (').
Указан путь к внешним книгам	Убедитесь, что каждая внешняя ссылка, вводимая в формулу, содержит имя книги и путь к ней.
Числа введены без форматирования	Не включайте форматы чисел в формулу. В аргументах формулы знаки доллара ($) используются для обозначения абсолютных ссылок, а запятые (,) — в качестве разделителей аргументов. Поэтому вместо $1 000 вводите в формулу просто число 1000.

К началу страницы

support.office.com

Формулы в Microsoft Excel

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

Посмотрите на таблицу, которую мы составили в предыдущем уроке.

Начиная с ячейки D2 и заканчивая D5, мы считали, используя одну и ту же формулу =Bn*Cn

Число n в нашем случае – это номер ячейки, то есть номер строки, в которой находится ячейка.

В этой табличке только четыре пункта: Сыр, Масло, Молоко, Хлеб. Поэтому нас не сильно затруднило посчитать сумму по каждому наименованию отдельно. Это даже полезно: ведь, как известно, повторение – мать учения. Но что делать, если в таблице не четыре наименования, а сто сорок четыре?! Неужели каждое значение придется считать отдельно?!

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

Удалите все значения в столбике «Сумма». Проще всего выделить ячейки с D2 по D5 и нажать кнопку Delete (Del) на клавиатуре. Таким образом содержимое очистится.

Очистить ячейки

Теперь еще раз посчитаем ячейку D2 при помощи формулы. Напомню, для этого мы печатали в D2 знак равно (=), нажимали на ячейку B2, затем печатали знак умножения (*), нажимали на C2 и нажимали кнопку Enter на клавиатуре.

Формула Excel

То есть наша формула выглядит следующим образом: =B2*C2

Проверить это можно, нажав на ячейку D2 и посмотрев в строку формул (вверху).

Строка формул

А теперь попробуем эту формулу применить к другим ячейкам (D3, D4, D5). Для этого щелкните по D2. Ячейка выделится рамочкой. В нижнем правом углу этой рамочки есть небольшой квадратик. Вот он-то нам и нужен.

Выделенная ячейка Excel

Если Вы наведете на этот квадратик курсор (плюсик), то он примет вид черного знака плюс (+).

Вот когда он стал таким плюсом, нужно нажать на левую кнопку мыши и, не отпуская ее, тянуть вниз до последней нужной нам ячейки (D5).

Применение формулы Excel

Отпустив левую кнопку мыши, программа посчитает значения в каждой ячейке, исходя из указанной формулы =Bn*Cn

Применение формулы Excel

Таким образом, Excel «понял», что нужно сделать, и успешно с этим справился.

А теперь другой пример. Напечатаем в ячейку А10 число 45, в А11 — 35, А12 — 25, А13 — 15. В ячейке В10 нужно посчитать, сколько будет А10/5. То есть, печатаем в В10 знак =, затем щелкаем по ячейке А10, делим на 5 и нажимаем Enter на клавиатуре. Итого, получилась формула =А10/5

Теперь выделим В10, то есть щелкнем по этой ячейке и, «схватившись» за маленький черный квадратик внизу, потянем до ячейки В13.

Эксель «применил» введенную формулу к другим ячейкам.

Нумерация строк в Экселе

Ну, и напоследок, еще один пример того, насколько программа Microsoft Excel многофункциональна.

Введите в ячейку F2 число 1, а в F3 – число 2.

Теперь выделите их.

Нажмите на маленький черный квадратик внизу выделенных ячеек и, не отпуская мышку, тяните вниз до ячейки F10.

Когда Вы отпустите левую кнопку мыши, вот что произойдет.

Excel «понял», что нам нужны числа по возрастанию и напечатал их за нас. Таким образом, он быстро пронумеровал строки в таблице.

www.neumeka.ru

пошаговая инструкция, особенности и рекомендации

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

Что такое Microsoft Excel?

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

Excel: знакомство с интерфейсом

Прежде всего, после открытия программы пользователь видит главное окно, в котором имеются основные элементы управления и инструменты для работы. При старте приложения в более поздних версиях появляется окно, в котором пользователю предлагается создать новый файл, который по умолчанию называется «Книга 1».Также здесь предлагается выбрать шаблон для дальнейших действий. На первом этапе знакомства с программой работа с таблицами Excel сводится к созданию пустой таблицы. Давайте сначала рассмотрим основные элементы программы. Сама таблица занимает основное поле. Таблица разбита на ячейки. Каждая ячейка обладает своей нумерацией благодаря двумерным координатам – буквенному обозначению столбца и номеру строки. Нумерация требуется для того, чтобы в формуле зависимости легко можно было определить именно ту ячейку с информацией, операция над которой будет осуществляться. Как и в остальных офисных приложениях, сверху располагается панель главного меню. Чуть ниже расположена панель инструментов. Под ней имеется специальная строка, в которой необходимо вводить формулы. Немного левее расположено окошко с координатами ячейки, активной на данный момент времени (это та ячейка, на которой находится прямоугольник). Внизу имеется панель листов, а также ползунок горизонтального перемещения. Под ними располагаются кнопки масштабирования и переключения вида. Вертикальная полоса для перемещения вверх/вниз по листу располагается справа.

Excel: простейшие операции и основные типы ввода данных

Вначале предполагается, что работа в Excel будет осваиваться начинающим пользователем по знакомым ему операциям. В таблице можно осуществлять операции копирования, вставки, вырезания данных, вводить числовую информацию и текст. Однако ввод в данном случае немного отличается от того, который осуществляется в текстовых редакторах. Проблема в том, что данная программа настроена изначально на автоматическое резервирование того, что пользователь прописывает в активной ячейке. Так, например, если в строку ввести 3/2/2016, то редактор автоматически распознает данные как дату. Вместо введенных цифр появится дата в упрощенном варианте 03/02/2016. Меняется формат отображения довольно просто, но на этом мы остановимся немного позже. С числами дело обстоит точно так же. Можно вводить любую числовую информацию с произвольным количеством символов после запятой. Она будет отображаться в том виде, в котором вы привыкли ее видеть. Если же вводится целое число, то оно будет представлено без нулей после запятой. Это тоже можно поменять. Многие начинающие пользователи после окончания ввода данных пытаются перейти на следующую ячейку, используя для этой цели клавиатурные стрелки. Так же можно сделать и в MS Word. Но здесь это не работает. Почему? Дело в том, что работа с таблицами в MS Excel ощутимо отличается от текстового редактора MS Word. Осуществить переход можно путем нажатия на клавишу Enter или при помощи установки активного прямоугольника на другую ячейку путем левого клика мыши. Если нажать на клавишу Esc после написания чего-то в активной ячейке, ввод автоматически будет отменен.

Работа с листами

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

Форматы ячеек MS Excel

Теперь обсудим самый главный вопрос. Одним из основных понятий в MS Excel является формат ячейки. Именно он определяет тип данных, который будет использоваться для распознавания содержимого. Чтобы вызвать редактирование формата, достаточно использовать меню правого клика. Здесь необходимо выбрать соответствующую строку. Также можно использовать клавишу F2. Слева в окне представлены все доступные форматы. Справа представлены основные варианты отображения данных. Если посмотреть на пример с датой, который был продемонстрирован выше, то в качестве формата ячеек выбирается «Дата», а справа устанавливается желаемый вариант отображения, например, 3 февраля 2016 г. Чтобы осуществлять математические операции, можно использовать несколько разных форматов. В самом простом случае можно выбрать числовой. Справа имеется несколько типов ввода, указатель количества знаков после запятой и поле установки разделителя. При использовании других числовых форматов, например, дробного, денежного, экспоненциального и так далее, можно также выставлять желаемые параметры. Для автоматического распознавания данных по умолчанию установлен общий формат. При вводе текста или нескольких литер программа может автоматически распознать информацию как нечто другое. По этой причине для активной ячейки при вводе текста необходимо установить соответствующий параметр.

Пример работы в Excel с таблицами и формулами

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

Построение диаграмм и графиков

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

Экспорт/импорт данных, перекрестные связи

В программе MS Excel предусмотрена возможность установки связи данных, находящихся на различных листах. Также можно использовать перекрестные ссылки на объекты или файлы другого формата, которые находятся на серверах в интернете. Файлы из программы Excel можно экспортировать в другие форматы, например PDF. Можно осуществлять копирование данных. Сама программа может открывать файлы, которые были созданы в других приложениях. Как вы сами можете убедиться, редактор имеет практически неограниченные возможности

computerologia.ru

30 функций Excel за 30 дней

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

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

Что это за курс?

Все 30 уроков являются переводом марафона статей канадского гуру Excel — Дебры Далглейш. Каждый день, с 2 января 2011 года по 31 января 2011 года, появлялась статья в блоге Contextures, описывающая одну из этих функций. Все функции относятся к категориям: текст, информация, а также поиск и ссылки. В разделе Список функций представлены ссылки на переводы всех этих статей.

Каждая статья содержит следующее:

Описание, которое подробно рассказывает, как работает каждая отдельная функция.
Все 30 уроков сопровождаются снимками с экранов, которые позволяют нагляднее передать важную информацию (снимки сделаны в Excel 2010).
Практические примеры применения формул Excel как в одиночку, так и с другими функциями.
Подводные камни, которые могут возникнуть при работе с функциями.
А также множество другой не менее полезной информации.

Что я получу?

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

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

Список функций:

День 01 – СОВПАД (EXACT) — может проверить две текстовые строки на предмет точного совпадения, причем, с учётом регистра.

День 02 – ОБЛАСТИ (AREAS) — возвращает количество областей в ссылке.

День 03 – СЖПРОБЕЛЫ (TRIM) — удаляет из текстовой строки все пробелы, кроме одинарных пробелов между словами.

День 04 – ИНФОРМ (INFO) — показывает информацию о текущей операционной среде.

День 05 – ВЫБОР (CHOOSE) — возвращает значение из списка, выбирая его в соответствии с числовым индексом.

День 06 – ФИКСИРОВАННЫЙ (FIXED) — округляет число до определённого количества десятичных знаков и возвращает результат в текстовом формате с разделителями разрядов или без.

День 07 – КОДСИМВ (CODE) — возвращает числовой код первого символа текстовой строки.

День 08 – СИМВОЛ (CHAR) — возвращает определённый символ, код которого соответствует введённому числу, опираясь на таблицу символов Вашего компьютера.

День 09 – ВПР (VLOOKUP) — ищет значение в первом столбце таблицы и возвращает другое значение из той же строки таблицы.

День 10 – ГПР (HLOOKUP) — ищет значение в первой строке таблицы и возвращает другое значение из того же столбца таблицы.

День 11 – ЯЧЕЙКА (CELL) — показывает информацию о форматировании, содержании и расположении ячейки по заданной ссылке.

День 12 – ЧИСЛСТОЛБ (COLUMNS) — возвращает количество столбцов в массиве или ссылке.

День 13 – ТРАНСП (TRANSPOSE) — возвращает горизонтальный диапазон ячеек как вертикальный или наоборот.

День 14 – Т (T) — возвращает текст, если значение в ячейке является текстовым, или пустую строку, если не текстовым.

День 15 – ПОВТОР (REPT) — повторяет текстовую строку заданное количество раз.

День 16 – ПРОСМОТР (LOOKUP) — возвращает значение из одной строки, одного столбца или из массива.

День 17 – ТИП.ОШИБКИ (ERROR.TYPE) — определяет тип ошибки по номеру или возвращает #N/A (#Н/Д), если ошибка не найдена.

День 18 – ПОИСК (SEARCH) — ищет текстовую строку внутри другой текстовой строки, и, если находит, то сообщает её позицию.

День 19 – ПОИСКПОЗ (MATCH) — возвращает позицию значения в массиве или ошибку #N/A (#Н/Д), если оно не найдено.

День 20 – АДРЕС (ADDRESS) — возвращает ссылку на ячейку в виде текста, основываясь на номере строки и столбца.

День 21 – ТИП (TYPE) — возвращает число, которое определяет тип данных.

День 22 – Ч (N) — возвращает значение, преобразованное в число.

День 23 – НАЙТИ (FIND) — находит текстовую строку внутри другой текстовой строки с учётом регистра.

День 24 – ИНДЕКС (INDEX) — возвращает значение или ссылку на значение.

День 25 – ЗАМЕНИТЬ (REPLACE) — заменяет символы внутри текста, основываясь на заданном количестве символов и начальной позиции.

День 26 – СМЕЩ (OFFSET) — возвращает ссылку, смещённую от заданной ссылки на определенное количество строк и столбцов.

День 27 – ПОДСТАВИТЬ (SUBSTITUTE) — заменяет старый текст на новый текст внутри текстовой строки.

День 28 – ГИПЕРССЫЛКА (HYPERLINK) — создаёт ссылку, открывающую документ, сохранённый на компьютере, сетевом сервере, в локальной сети или в интернет.

День 29 – ПЕЧСИМВ (CLEAN) — удаляет некоторые непечатаемые символы из текста.

День 30 – ДВССЫЛ (INDIRECT) — возвращает ссылку, заданную текстовой строкой.