Новости

Кому важно знать Microsoft Excel?

Администраторы баз данных

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

Бухгалтеры

Для бухгалтеров Excel незаменим – он может работать как в
комплексе с профессиональными учетными системами типа 1С, так и вместо них. Бухгалтерский
софт позволяет экспортировать данные в сводные таблицы Excel, чтобы проводить глубокий
анализ по различным параметрам и составлять отчеты. Также с помощью Эксель производятся сложные расчеты с
применением различных поправок и коэффициентов, которые можно шаблонизировать для
циклически повторяемых операций. Кроме того, табличный редактор часто используется
для создания документов – прайс-листов, счетов-фактур и других форм.

Экономисты и финансовые аналитики

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

Банковские служащие

Эксель обладает множеством функций для расчетов различных показателей
для банковских продуктов. К примеру, для подсчета выгодных процентных ставок с
учетом платежеспособности физлица или предприятия, определения ежемесячных
платежей, исходя из процентной ставки, числа периодов и суммы займа (функция
ПЛТ), вычисления сроков погашения кредита (функция КПЕР), подсчета дохода банка для кредитов с заданной суммой, ежемесячными
выплатами и сроком погашения (функция СТАВКА). Также в Excel легко рассчитать сумму, которую клиент может взять в кредит с
установленными банком процентными ставками, исходя из суммы, которую он готов
выплачивать ежемесячно (функция ПС).

Менеджеры по продажам/закупкам

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

Функции Excel

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

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

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

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

=СУММ(В1:В5)

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

Аргумент функции заключен в круглые скобки.
Открывающая скобка отмечает начало аргумента и ставится сразу после имени
функции. В случае ввода пробела или другого символа между именем и открывающей
скобкой в ячейке будет отображено ошибочное значение #ИМЯ? Некоторые функции не
имеют аргументов. Даже в этом случае функция должна содержать круглые скобки:

=С5*ПИ()

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

Зачем изучать Microsoft Excel?

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

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

Фундаментальный инструмент Excel

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

Так, пересчитывая курс, в одной ячейке можно указать цену, во второй курс валюты, а в третьей задать формулу пересчета (= первая ячейка * вторая ячейка), далее нажать Enter и получить цену в рублях. В первом листе в нужной ячейке можно поставить “=”, перейти на второй лист и указать третью ячейку с итогом. Опять нажать Enter и получить результат.

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

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

В строке формул ставим равно и ссылку на ячейку из таблицы с исходными данными (=А3). После этого получим просто дублирование значения из таблицы. При протягивании этой ячейки получится копия таблицы с данным, которые будут изменяться соответственно со сменой информации в исходной таблице. Это пример протягивания ячеек без фиксирования диапазонов.

Можно закрепить ссылку, чтобы оставить ее неизменной при протягивании полностью, по строке или по столбцу. Фиксирование выполняется в строке формул с помощью знака $. Этот знак ставят перед той частью координат в ссылке, которую необходимо зафиксировать:
$ перед буквой – фиксирование по столбцу — $С1
$ перед цифрой – фиксирование по строке — С$1
$ перед буквой и цифрой – полное фиксирование ячейки — $С$1.

3 главные функции Excel для финансистов и бухгалтеров

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

  • Функция СУММ
  • Функция ЕСЛИ
  • Функция СУММЕСЛИ

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

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

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

1. Функция СУММ

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

2. Функция ЕСЛИ

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

Поддерживает множество внутренних вложений и очень похожа на «матрёшку», что всё равно не уменьшает ее полезность и универсальность.

3. Функция СУММЕСЛИ

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

Восстановление несохранённых файлов

Представьте: вы закрываете отчёт, с которым возились последнюю половину дня, и в появившемся диалоговом окне «Сохранить изменения в файле?» вдруг зачем-то жмёте «Нет». Офис оглашает ваш истошный вопль, но уже поздно: несколько последних часов работы пошли псу под хвост.

На самом деле есть шанс исправить ситуацию. Если у вас Excel 2010, то нажмите на «Файл» → «Последние» (File → Recent) и найдите в правом нижнем углу экрана кнопку «Восстановить несохранённые книги» (Recover Unsaved Workbooks).

В Excel 2013 путь немного другой: «Файл» → «Сведения» → «Управление версиями» → «Восстановить несохранённые книги» (File — Properties — Recover Unsaved Workbooks).

В последующих версиях Excel следует открывать «Файл» → «Сведения» → «Управление книгой».

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

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

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

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

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

Если данные нужно сортировать по среднему столбцу, то можно использовать меню “Данные” — пункт “Сортировка” — “Сортировка диапазона”. В разделе “Сортировать по” необходимо выбрать столбец и тип сортировки.

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

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

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

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

Набор горячих клавиш Excel, без которых вам не обойтись

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

  • F4 — при вводе формулы, регулирует тип ссылок (относительные, фиксированные). Можно использовать для повтора последнего действия.
  • Shift+F2 — редактирование примечаний
  • Ctrl+; — ввод текущей даты (для некоторых компьютеров Ctrl+Shift+4)
  • Ctrl+’ — копирование значений ячейки, находящейся над текущей (для некоторых компьютеров работает комбинация Ctrl+Shift+2)
  • Alt+F8 — открытие редактора макросов
  • Alt+= — суммирование диапазона ячеек, находящихся сверху или слева от текущей ячейки
  • Ctrl+Shift+4 — определяет денежный формат ячейки
  • Ctrl+Shift+7 — установка внешней границы выделенного диапазона ячеек
  • Ctrl+Shift+0 — определение общего формата ячейки
  • Ctrl+Shift+F — комбинация открывает диалоговое окно форматирования ячеек
  • Ctrl+Shift+L — включение/ отключение фильтра
  • Ctrl+S — сохранение файла (сохраняйтесь как можно чаще, чтобы не потерять ценные данные).

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

Бухгалтерия в Excel — 5 полезных приемов

В настоящее время количество различных отчетов, которые готовятся всеми подразделениями организаций, неуклонно растет. Очень часто на предприятиях осуществляется автоматизация отчетности на базе различных программных продуктов (SAP, 1С, Инталев и прочее).

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

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

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

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

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

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

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

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

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

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

Решение математических задач в Excel

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

Условие учебной задачи. Найти обратную матрицу В для матрицы А.

  1. Делаем таблицу со значениями матрицы А.
  2. Выделяем на этом же листе область для обратной матрицы.
  3. Нажимаем кнопку «Вставить функцию». Категория – «Математические». Тип – «МОБР».
  4. В поле аргумента «Массив» вписываем диапазон матрицы А.
  5. Нажимаем одновременно Shift+Ctrl+Enter — это обязательное условие для ввода массивов.

Возможности Excel не безграничны. Но множество задач программе «под силу». Тем более здесь не описаны возможности которые можно расширить с помощью макросов и пользовательских настроек.

Решение финансовых задач в Excel

Чаще всего для этой цели применяются финансовые функции. Рассмотрим пример.

Условие. Рассчитать, какую сумму положить на вклад, чтобы через четыре года образовалось 400 000 рублей. Процентная ставка – 20% годовых. Проценты начисляются ежеквартально.

Оформим исходные данные в виде таблицы:

Так как процентная ставка не меняется в течение всего периода, используем функцию ПС (СТАВКА, КПЕР, ПЛТ, БС, ТИП).

Заполнение аргументов:

  1. Ставка – 20%/4, т.к. проценты начисляются ежеквартально.
  2. Кпер – 4*4 (общий срок вклада * число периодов начисления в год).
  3. Плт – 0. Ничего не пишем, т.к. депозит пополняться не будет.
  4. Тип – 0.
  5. БС – сумма, которую мы хотим получить в конце срока вклада.

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

Для проверки правильности решения воспользуемся формулой: ПС = БС / (1 + ставка)кпер. Подставим значения: ПС = 400 000 / (1 + 0,05)16 = 183245.

Подсчет календарных дней

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

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

Рекомендация: набирайте дату на цифровой части клавиатуры так: 12/10/2016. Программа сама превратит введенные данные в формат даты и получится 12.10.2016.

Далее выбираем третью ячейку и жмем “Вставить функцию”, вы можете найти ее по значку ¶x. После нажатия всплывет окно “Мастер функций”. Из списка “Категория” выбираем “Дата и время”, а из списка “Функция”— “ДНЕЙ360” и нажимаем кнопку Ок. В появившемся окне нужно вставить значения начальной и конечной даты. Для этого нужно просто щелкнуть по ячейкам таблицы с этими датами, а в строке “Метод” поставить единицу и нажать Ок. Если итоговое значение отражено не в числовом формате, нужно проверить формат ячейки: щелкнуть правой кнопкой мыши и выбрать из меню “Формат ячейки”, установить “Числовой формат” и нажать Ок.

Еще можно выполнить подсчет дней таким способом: в третьей ячейке набрать = ДНЕЙ 360 (В1; В2; 1). В скобках необходимо указать координаты двух первых ячеек с датами, а для метода поставить значение единицы. При расчете процентов за недели можно полученное количество дней разделить на 7.

Решение задач оптимизации в Excel

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

В Excel для решения задач оптимизации используются следующие команды:

Для решения простейших задач применяется команда «Подбор параметра». Самых сложных – «Диспетчер сценариев». Рассмотрим пример решения оптимизационной задачи с помощью надстройки «Поиск решения».

Условие. Фирма производит несколько сортов йогурта. Условно – «1», «2» и «3». Реализовав 100 баночек йогурта «1», предприятие получает 200 рублей. «2» — 250 рублей. «3» — 300 рублей. Сбыт, налажен, но количество имеющегося сырья ограничено. Нужно найти, какой йогурт и в каком объеме необходимо делать, чтобы получить максимальный доход от продаж.

Известные данные (в т.ч. нормы расхода сырья) занесем в таблицу:

На основании этих данных составим рабочую таблицу:

  1. Количество изделий нам пока неизвестно. Это переменные.
  2. В столбец «Прибыль» внесены формулы: =200*B11, =250*В12, =300*В13.
  3. Расход сырья ограничен (это ограничения). В ячейки внесены формулы: =16*B11+13*B12+10*B13 («молоко»); =3*B11+3*B12+3*B13 («закваска»); =0*B11+5*B12+3*B13 («амортизатор») и =0*B11+8*B12+6*B13 («сахар»). То есть мы норму расхода умножили на количество.
  4. Цель – найти максимально возможную прибыль. Это ячейка С14.

Активизируем команду «Поиск решения» и вносим параметры.

После нажатия кнопки «Выполнить» программа выдает свое решение.

Оптимальный вариант – сконцентрироваться на выпуске йогурта «3» и «1». Йогурт «2» производить не стоит.

Ссылка на основную публикацию