Таблица канцтоваров в excel

Работа добавлена на сайт samzan.net: 2016-03-13

1.  EXCEL

Создать (по образцу) таблицу для расчета затрат на приобретение канцтоваров.

Построить диаграмму             

2.   EXCEL

Создать (по образцу) таблицу для расчета заработной платы.

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

3. EXCEL

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

4. EXCEL

5. EXCEL

6. EXCEL

Подготовьте таблицу для расчета суммы процентных выплат продавцам разных отделов некоторого магазина, по принципу: если выручка в отделе более 10 000 рублей, то продавцу причитается 2% от суммы, в противном случае 3%. Предусмотреть подсчет итоговых сумм, максимальной и минимальной выручки.

Практическая отчетная работа в MS Excel. 

Практическая работа выполняется в одном
документе с названием «Практическая отчетная работа в MS Excel», каждое
задание выполняется на отдельном Листе, название листа соответствует номеру
задания «Задание №1» и т.д.

Задание №1

•       Составьте
прайс-лист по образцу:

•      
Выделите ячейку В1 и введите в нее заголовок
таблицы Прайс-лист магазина «Канцтовары»

•      
В ячейку С2 введите функцию СЕГОДНЯ
(Поставьте знак «=», на панели инструментов вкладка ФОРМУЛЫ
выберите Дата и Время вставить функцию СЕГОДНЯ).

•      
В ячейку В3 введите слова «Курс евро»,
в С3 – курс евро на сегодняшний день — 76.

•      
К ячейке С3 примените денежный формат (Главная,
Вкладка Число, Числовой формат, Денежный. (Обозначение можно выбрать
произвольное).

•      
В ячейки А5:С5 введите заголовки столбцов таблицы.

•      
Выделите их и примените полужирный стиль начертания и более
крупный шрифт.

•      
В ячейки А6:А12 и В6:В12 введите
данные.

•      
В ячейку С6 введите формулу: = В6*$C$3 .
($ означает, что используется абсолютная ссылка).

•      
Выделите ячейку С6 и протяните за маркер заполнения
вниз до ячейки С13.

•      
Выделите диапазон ячеек С6:С12 и примените к ним
денежный формат.

•      
Выделите заголовок – ячейки В1:С1 и выполните
команду: щелчок правой клавишей,  Формат Ячеек, вкладка Выравнивание
и установите переключатель «по центру выделения» (Горизонтальное
выравнивание), «Переносить по словам». Увеличьте шрифт заголовка.

Задание №2

 Рассчитайте ведомость выполнения плана
товарооборота

•      
Заполнение столбца Mi можно выполнить протяжкой
маркера.

•      
Значения столбцов Vi и Oi вычисляются
по формулам: Vi=Fi / Pi;  Oi=Fi – Pi

Задание №3

Посчитайте, хватит ли вам 550 рублей, чтоб купить все
продукты, которые вам заказала мама,

и хватит ли купить чипсы за 30 рублей?

Задание №4

Дополните таблицу «Расписание» расчетами
времени стоянок поезда в каждом населенном пункте. Вычислите суммарное время
стоянок, общее время в пути, время, затрачиваемое поездом на передвижение от
одного населенного пункта к другому.

Выполнения задания:

1.           
Измените формат чисел для блоков С2:С9 и Е2:Е9. Для этого
выполните следующие действия:

—  Выделите блок ячеек
С2:С9;

—  Выполните
команду основного меню Главная – Число – Формат ячеек- вкладка
Число — Числовые форматы — Время и установите параметры (часы:минуты).

2.           
Посчитайте время стоянок: 

—  в ячейке С3
введите формулу: = D3-В3              — аналогично посчитайте
ячейки С4:С7

3.           
Посчитайте время в пути:

—  в ячейке Е2
введите формулу: =В3-D2

—  аналогично
посчитайте ячейки Е3:Е7 4.      Вычислите суммарное время стоянок.           —
Выберите ячейку С9;

—  Щелкните
кнопку  : Формулы —
Автосумма на панели инструментов;          — Выделите блок ячеек С3:С7 и
нажмите клавишу Enter. 5.      Вычислите суммарное время в
пути (аналогично 4 пункту)

6.      Оформите таблицу цветом (выделить — щелчок правой
клавишей мыши — формат ячеек — заливка — выбрать цвет — ок)  и выделите границы
таблицы (выделить таблицу — щелчок правой клавишей мыши — формат ячеек —
граница — выбрать границы — ок).

Итог: 

Задание №5

Виды ссылок

1.    Заданы стоимость 1 кВт./ч. электроэнергии и показания
счетчика за предыдущий и текущий месяцы. Необходимо вычислить расход
электроэнергии за прошедший период и стоимость израсходованной электроэнергии. 

Выполнение работы:

1.                 
Выровняйте текст в ячейках. Выделите ячейки А3:Е3. Главная
— Формат –Формат ячейки – Выравнивание: по горизонтали – по центру, по
вертикали – по центру, отображение – переносить по словам.

2.                 
В ячейку А4 введите: Кв. 1, в ячейку А5
введите: Кв. 2. Выделите ячейки А4:А5 и с помощью маркера
автозаполнения заполните нумерацию квартир по 7 включительно.

5.           
Заполните ячейки B4:C10 по рисунку.

6.           
В ячейку D4 введите формулу для нахождения расхода
эл/энергии. И заполните строки ниже с помощью маркера автозаполнения.

7.           
В ячейку E4 введите формулу для нахождения
стоимости эл/энергии =D4*$B$1 И заполните строки ниже с помощью
маркера автозаполнения. 

8.           
В ячейке А11 введите текст «Статистические данные»
выделите ячейки A11:B11 и щелкните на панели инструментов кнопку
«Объединить и поместить в центре».

9.           
В ячейках A12:A15 введите текст, указанный на
рисунке.                   10.  Щелкнуть мышью по ячейке B12 и
ввести математическую функцию СУММ, для этого необходимо щелкнуть
в строке формулпо знаку fx и выбрать
функцию, а также подтвердить диапазон ячеек В4:В10.

11.  Аналогично функции задаются и в ячейках B13:B15.
12. Таблицу заполнить цветом, выделить границы.

Итог:

Задание №6

Заполнить таблицу по образцу, рассчитать возраст каждого.

Выполнение:

1.      Заполнить
таблицу по образцу. В С2:С11 отметить формат Время (главная
– число — дата)

2.      В
  формулу:

С2)-1900)

Эта формула будет вычислять всегда правильное количество
полных лет человека, т.к. для вычисления используется функция СЕГОДНЯ, которая
в каждый конкретный момент времени использует текущую дату. (Таблица была
составлена 01.10.2015, при использовании этого примера позже 5.12.2015 будут
другие данные в столбце С)

3.      Протяните
значения на С2:С11

4.      Заполните
таблицу цветом

Итог: 

Задание №7

Посчитать формулу  

Выполнение:

1.  Заполните таблицу
аналогично картинке. Заполните цветом

2.  Посчитайте по формуле
значение m 

  Итог:  

Задание №8

Построить график для формулы

Выполнение:

1. В задании №7 построить диаграммы

—                    
для столбца m (выделить весь столбец, на
панели инструментов выбрать вкладку Вставка – Диаграммы – График – выбрать
любой —  ОК)

—                    
для всех столбцов: выделить все столбцы, Вставка
– Диаграммы – Гистограмма – выбрать любую —  ОК)

—                    
для столбцов h  и ω: выделить
столбцы, Вставка – Диаграммы – Линейчатая – выбрать любую
—  ОК)

—                    
для столбцов c и m: выделить столбцы,
Вставка – Диаграммы – Другие – Поверхность — выбрать любую —  ОК)

Задание №9

Построить график «ЗОНТИК» по приведенной функции:

у1= -1/18х2 + 12, х[-12;12]

Порядок выполнения действий:

1.  Устанавливаем курсор
в ячейку В1 и вводим у1 

2.  В ячейку В2 вводим
формулу   

3.  Растягиваем формулу
до ячейки В26

4.  Аналогично в ячейку С10 (т.к.
значение функции находим только на отрезке х[-4;4]) 
вводим формулу для графика функции y2= -1/8х2 +6.                                  и
т.д.

В результате должна получиться следующая ЭТ

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

Выделяем диапазон ячеек А1:G26

На панели инструментов выбираем меню Вставка →
Диаграмма.

В окне Мастера диаграмм выберите Точечная → Выбрать
нужный вид→ Нажать Ok.

В результате должен получиться следующий рисунок:

Задание №10

В электронных таблицах с использованием
в формулах смешанных ссылок создать таблицу умножения:

1.  Заполните название
таблицы, строку и столбец с множителями:

2.  В ячейку В3 запишите
формулу =A3*В2 растяните ее в право и сразу, не снимая выделения, вниз:

3.  Допустили
ошибку. Для ее исправления вернемся в ячейку В3 и проанализируем формулу =А3*В2
при растягивании этой формулы вправо должна остаться неизменной ссылка на
столбец  А: =$А3*В2 при растягивании формулы вниз необходимо зафиксировать
ссылку на строку 2: =$А3*В$2 растяните полученную формулу вправо и вниз

3. Убедитесь, что теперь формулы заполнены верно:

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

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

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

Как вести складской учет в Excel?

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

1. Заполнять справочники максимально точно и подробно. Если это номенклатура товаров, то необходимо вносить не только названия и количество. Для корректного учета понадобятся коды, артикулы, сроки годности (для отдельных производств и предприятий торговли) и т.п.
2. Начальные остатки вводятся в количественном и денежном выражении. Имеет смысл перед заполнением соответствующих таблиц провести инвентаризацию.
3. Соблюдать хронологию в регистрации операций. Вносить данные о поступлении продукции на склад следует раньше, чем об отгрузке товара покупателю.
4. Не брезговать дополнительной информацией. Для составления маршрутного листа водителю нужна дата отгрузки и имя заказчика. Для бухгалтерии – способ оплаты. В каждой организации – свои особенности. Ряд данных, внесенных в программу складского учета в Excel, пригодится для статистических отчетов, начисления заработной платы специалистам и т.п.

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

1. Для корректного ведения складского учета в Excel нужно составить справочники. Они могут занять 1-3 листа. Это справочник «Поставщики», «Покупатели», «Точки учета товаров». В небольшой организации, где не так много контрагентов, справочники не нужны. Не нужно и составлять перечень точек учета товаров, если на предприятии только один склад и/или один магазин.
2. При относительно постоянном перечне продукции имеет смысл сделать номенклатуру товаров в виде базы данных. Впоследствии приход, расход и отчеты заполнять со ссылками на номенклатуру. Лист «Номенклатура» может содержать наименование товара, товарные группы, коды продукции, единицы измерения и т.п.
3. Поступление товаров на склад учитывается на листе «Приход». Выбытие – «Расход». Текущее состояние – «Остатки» («Резерв»).
4. Итоги, отчет формируется с помощью инструмента «Сводная таблица».

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

Теперь независимо от количества записей пользователь будет видеть заголовки столбцов.



Таблица Excel «Складской учет»

Рассмотрим на примере, как должна работать программа складского учета в Excel.

Делаем «Справочники».

Для данных о поставщиках:

* Форма может быть и другой.

Для данных о покупателях:

* Обратите внимание: строка заголовков закреплена. Поэтому можно вносить сколько угодно данных. Названия столбцов будут видны.

Для аудита пунктов отпуска товаров:

Еще раз повторимся: имеет смысл создавать такие справочники, если предприятие крупное или среднее.

Можно сделать на отдельном листе номенклатуру товаров:

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

Диапазону таблицы «Номенклатура» присвоим имя: «Таблица1». Для этого выделяем диапазон таблицы и в поле имя (напротив строки формул) вводим соответствующие значение. Также нужно присвоить имя: «Таблица2» диапазону таблицы «Поставщики». Это позволит удобно ссылаться на их значения.

Для фиксации приходных и расходных операций заполняем два отдельных листа.

Делаем шапку для «Прихода»:

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

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

Создаем выпадающий список для столбца «Наименование». Выделяем столбец (без шапки). Переходим на вкладку «Данные» — инструмент «Проверка данных».

В поле «Тип данных» выбираем «Список». Сразу появляется дополнительное поле «Источник». Чтобы значения для выпадающего списка брались с другого листа, используем функцию: =ДВССЫЛ(«номенклатура!$A$4:$A$8»).

Теперь при заполнении первого столбца таблицы можно выбирать название товара из списка.

Автоматически в столбце «Ед. изм.» должно появляться соответствующее значение. Сделаем с помощью функции ВПР и ЕНД (она будет подавлять ошибку в результате работы функции ВПР при ссылке на пустую ячейку первого столбца). Формула: .

По такому же принципу делаем выпадающий список и автозаполнение для столбцов «Поставщик» и «Код».

Также формируем выпадающий список для «Точки учета» — куда отправили поступивший товар. Для заполнения графы «Стоимость» применяем формулу умножения (= цена * количество).

Формируем таблицу «Расход товаров».

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

Делаем «Оборотную ведомость» («Итоги»).

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

Столбцы «Поступление» и «Отгрузки» заполняется с помощью функции СУММЕСЛИМН. Остатки считаем посредством математических операторов.

Скачать программу складского учета (готовый пример составленный по выше описанной схеме).

Вот и готова самостоятельно составленная программа.