5. Для последнего интервала большая граница тоже должна быть включена в диапазон исследуемых значений.
6. Чтобы формулу из ячейки D2 автозаполнением скопировать по столбцу D, ссылки на ячейки А10 и В10 нужно сделайте абсолютными;
7. Аналогичным образом введите формулы в столбцы E, F, G.
8. Для подсчета числа попаданий в каждый интервал выполните следующие действия:
· выделите блок D2:D6;
· нажмите кнопку Автосумма на Стандартной панели инструментов;
· повторите это действие для каждого столбца.
8. Значения столбца Проверка получить, используя операцию Автосумма для значений блоков строк D2:G2, D3:G3 и т.д.
9. Значение ячейки Итого столбца Проверка должно совпадать с количеством сотрудников.
Задания для самостоятельной работы
Задание № 1.
Продукцией городского молочного завода являются молоко, кефир и сметана. На производства 1 т. молока, кефира и сметаны требуется соответственно 1010, 1020, и 9450 кг молока.
Прибыль от реализации 1 т. молока, кефира и сметаны соответственно равны 300, 220 и 1360 руб. Было изготовлено молока 123 т., кефира 342 т., сметаны 256 т.
как … присвоить ячейке логическое значение в Excel
а) при помощи электронной таблицы рассчитать:
— прибыль от реализации каждого вида изделий,
— долю (в процентах) прибыльности каждого вида изделий от общей суммы.
— расход молока (сырья).
б).построить диаграмму по расходу сырья для каждого вида изделия.
Задание № 2.
На книжную базу поступили 3 наименования книг: словари, книги по кулинарии и пособия по вязанию. Они были распределены по трем магазинам: «Книжный мир», «Дом книги» и «Глобус». В «Книжный мир» поступило словарей — 10400 экземпляров, кулинарных книг — 23650 экземпляров, пособий по вязанию — 1500 экземпляров; в «Дом книги» — 10300 словарей, 22950 кулинарных книг и 1990 пособий по вязанию; в «Глобус» соответственно 9100, 23320 и 2500 экземпляров.
В первом магазине было продано словарей — 8945 экземпляров, кулинарных книг — 19865 экземпляров, пособий по вязанию — 873 экземпляра; во втором магазине было продано словарей — 9300 экземпляров, кулинарных книг — 21900 экземпляров, пособий по вязанию — 1020 экземпляра; в третьем магазине соответственно было продано 8530, 18100 и 2010 экземпляров.
Требуется:
а) при помощи электронной таблицы рассчитать:
— общие количества книг каждого наименования поступивших на книжную базу;
— процент продажи каждого наименования книг в каждом магазине;
— количества книг, оставшихся после реализации ;
б) построить диаграмму по распределению книг в магазинах.
Задание № 3.
На предприятии работники имеют следующие оклады: начальник отдела – 1000 р., инженер 1кат. – 860 р., инженер – 687 р., техник – 315 р., лаборант – 224 р. Предприятие имеет два филиала: в средней полосе и в условиях крайнего севера. Все работники получают надбавку 10% от оклада за вредный характер работы, 25 % от оклада ежемесячной премии. Со всех работников удерживают 20 % подоходный налог, 3% профсоюзный взнос и 1% к пенсии. Работники филиала, расположенного в средней полосе получают 15 % районного коэффициента, работники филиала, расположенного в районе крайнего севера имеют 70% районный коэффициент и 50% северной надбавки от начислений.
Функция ЕСЛИ в Excel ➤ Несколько условий, логические функции И и ИЛИ
Расчет заработной платы должен быть произведен для каждого филиала в отдельности. Результатом должно быть две таблицы.
а) при помощи электронной таблицы рассчитать суммы к получению каждой категории работников.
б) построить две диаграммы, отражающие отношение районного коэффициента (районной и северной надбавки) и зарплаты для всех сотрудников обоих филиалов.
Задание № 4.
Производственная единица изготавливает изделия трех видов П1, П2 и П3. Затраты на изготовление единицы продукций П1, П2 и П3 составляют 7, 15 и 10(руб.) соответственно.
Прибыль от реализации одного изделия данного вида соответственно равны 20, 16 и 25(руб.). План производства изделий П1-200482 штуки, П2-43292 шт., П3-1463012 шт.. В январе было изготовлено П1- 135672 шт., П2- 60712 шт., П3- 1456732 шт..
а) при помощи электронной таблицы рассчитать в рублях и долларах (курс доллара — величина изменяющаяся):
— плановые затраты на производство,
— прибыль от реализации каждого вида изделий,
— прибыль, полученную предприятием в январе.
— процент выполнения плана в январе по каждому виду изделия.
б) построить диаграмму по прибыли каждого вида изделия.
Задание № 5.
Кондитерская фабрика для производства трех видов карамели А, В и С использует три вида сырья: сахарный песок, патоку и фруктовое пюре.
Нормы расхода сырья на 1 т. карамели соответственно равны(т):
| A | B | C | |
| сахарный песок | 0.6 | 0.5 | 0.6 |
| патока | 0.2 | 0.4 | 0.3 |
| фруктовое пюре | 0.2 | 0.1 | 0.1 |
Общее количество сырья каждого вида, которое может быть использовано фабрикой соответственно равно 1500, 900 и 300 тонн. За месяц фабрика изготовила карамели вида А- 820, В- 900, С- 400 (т).
а) при помощи электронной таблицы рассчитать :
— расход сырья каждого вида ,
— количество оставшегося сырья,
— количество карамели вида А, на производство которого хватит оставшегося сахара .
б) построить диаграмму по расходу сырья каждого вида для производства карамели А, В, С.
Задание № 6.
Фирма “ Новый путь” совершила закупку новых автомобилей: ВАЗ — 21093, ГАЗ — 31029 и ВАЗ — 2106. Автомобилей марки ВАЗ — 21093 было закуплено 35 штук по цене 2000$; автомобилей марки ВАЗ — 2106 — было закуплено 21(шт.) по цене 1600$; автомобилей марки ГАЗ — 31029 — было закуплено 10(шт.) по цене 2100$.
На машины были поставлена сигнализация и врезаны люки. После чего они были проданы по цене ВАЗ — 21093 — 45000 руб; ВАЗ — 2106 — 39000руб и ГАЗ — 31029- 48000руб.
а) при помощи электронной таблицы рассчитать:
— сумму затрат на покупку каждой марки автомобилей;
— общую сумму затрат на покупку всех автомобилей;
— полученную после продажи машин прибыль.
б) Построить диаграмму по объему продаж автомобилей всех марок
Задание № 7.
Часовой завод изготовил в январе часы вида А- 150 шт, вида В — 230 шт, вида С — 180 шт. В феврале производство продукции выросло: вида А на 5%, вида В на 3%, С на 2 %. В марте рост составил соответственно 1,5; 1,6 и 2%. Затраты на изготовление каждого вида часов составляют А — 85 р, В — 73 р, С — 84 р. Продажная стоимость каждого вида изделий составляет соответственно 120 р., 100 р и 110 р.
а) при помощи электронной таблицы рассчитать в рублях и долларах:
— какое количество часов изготовлено в каждый месяц;
— прибыль от реализации каждого вида изделий в рублях и долларах;
— ежемесячные затраты на производство каждого вида изделий;
б) построить диаграмму по прибыли каждого вида изделия.
Задание № 8.
На предприятии работники имеют следующие оклады: начальник отдела – 1000 р., инженер 1кат. – 860 р., инженер – 687 р., техник – 315 р., лаборант – 224 р.
Все работники получают надбавку 10% от оклада за вредный характер работы. Все работники получают 50 % премии в том месяце, когда выполняется план.
При невыполнении плана из зарплаты вычитают 10 % от начислений. Со всех работников удерживают 12 % подоходный налог, 3% профсоюзный взнос и 1% к пенсии. Все удержания производятся от начислений.
а) при помощи электронной таблицы рассчитать суммы к получению каждой категории работников по месяцам;
б) построить две диаграммы, отражающие отношение зарплаты всех работников в удачный месяц и в неудачный.
Задание № 9.
Производственная единица изготавливает изделия трех видов A,B и C. Затраты на изготовление единицы продукций A, B и С составляют 5, 10 и 11(руб.) соответственно.
Прибыль от реализации одного изделия данного вида соответственно равны 10, 14 и 12(руб.). План производства изделий A-148265, B-543292, C-463012. Было изготовлено A- 135672, B- 608712, C- 456732.
а) при помощи электронной таблицы рассчитать:
— прибыль от реализации каждого вида изделий;
— процент выполнения плана по каждому виду изделия.
б) построить диаграмму, отражающую прибыль от реализации каждого вида изделий
в) распечатать документ.
Задание №10.
Годовой доход семьи из четырех человек составляет в среднем 150000 денежных единиц. Основные затраты состоят из: коммунальные услуги — 13700 (ден.ед.); плата за телефон — 9600 (ден.ед.); питание — 64000 (ден.ед.); плата за детские учреждения — 5800 (ден.ед.); Остальная сумма тратится , исходя из нужд и потребностей семьи.
а). Представить данные в виде таблицы;
— среднемесячный доход семьи;
— сумму основных затрат;
— долю каждой строки расходов (в процентах) от общей суммы;
Рассчитать все те же показатели в долларах, для до кризисной ситуации, считая курс равным 6р. за доллар, и в после кризисный период, исходя из того, что курс можно изменить несколько раз в течении дня, а результаты расчёта выполниться автоматически.
Задание №11.
1. В новой рабочей книге создать таблицу. (Табл. 1).
2. Посчитать количество отличных, хороших, удовлетворительных и неудовлетворительных оценок по двум предметам и вывести результат в отдельную строку «ВСЕГО:».
3. Посчитать процент всех оценок в сумме по двум предметам. Вывести процент в отдельную строку (расчёт процента: числитель меньше, чем знаменатель; затем находим в панели инструментов форматирования кнопку с изображением %).
4. Отредактировать таблицу: задать границу, цвет ячеек, размер столбцов и строк и т. д.
Процент всех оценок
Задание № 12. Определите, какая сумма окажется на счёте, если вклад размером 900 тыс. руб. положен под 9% годовых на 19 лет, а проценты начисляются ежеквартально.
Задание № 13. Взносы на сберегательный счёт составляют 200 тыс. руб. в начале каждого года. Определите, сколько будет на счёте через семь лет при ставке процента 10%.
Задание № 14. Предполагается, что в течении первых двух лет на счёт откладывается по 800 тыс. руб. в конце каждого года, а в следующие три года – по 850 тыс. руб. в конце каждого года. Определить будущую стоимость этих вложений к концу пятого года, если ставка процента 11%.
Задание № 15. Рассчитайте будущую стоимость облигации номиналом 100 тыс. руб. выпущенной на семь лет, если в первые три года проценты начисляются по ставке 17%, а остальные четыре года – по ставке 22% годовых.
Задание № 16. Определите текущую стоимость обязательных ежемесячных платежей размером 120 тыс. руб. в течении четырёх лет, если годовая процентная ставка – 14%.
Задание № 17. Допустим, рассматривается проект стоимости 100 млн. руб. ожидается, что ежемесячные доходы по проекту составят 16, 25, 36, 49 млн. руб. за четыре месяца. Определите чистую текущую стоимость проекта, если годовая норма процента 195.
Задание № 18. Для покупки компании была взята ссуда 97 млн. руб. под 13% годовых. Доходы от приобретения составили 15, 18, 29, 50 млн. руб. за четыре года и были реинвестированы под 15% годовых. Найдите модифицированную внутреннюю скорость оборота инвестиции.
Задание № 19. Допусти, проект стоимостью 9 млн. руб. будут в течение следующих трёх лет приносить доходы – 4,4; 3,2; 5,9 млн. руб. ежегодно4 а на четвёртый год предполагается убыток в 1, 6 млн. руб. оцените целесообразность принятия проекта, если рыночная норма процента 13%.
Задание № 20. Рассчитаем, какая сумма окажется на счёте, если 27 тыс. руб. положены на 33 года под 13,5% годовых. Проценты начисляются каждые три года.
Контрольные вопросы.
1. Назначение электронной таблицы?
2. Как называется документ в программе Excel? Из чего он состоит?
3. Особенности типового интерфейса табличных процессоров?
4. Какие типы данных могут содержать электронные таблицы?
5. Какие данные называют зависимыми, а какие независимыми?
6. По какому признаку программа определяет, что введенные данные являются не значением, а формулой?
7. Что в Excel используется в формулах в качестве операндов?
8. Что такое формула в электронной таблице и её типы? Приведите примеры.
9. Что такое функция в электронной таблице и её типы? Приведите примеры.
10. Поясните, для чего используется абсолютные и относительные адреса ячеек?
11. Что такое автозаполнение?
12. Приоритет выполнения операций в арифметических формулах Excel?
13. Как можно «размножить» содержимое ячейки?
14. Как посмотреть и отредактировать формулу, содержащуюся в ячейке?
15. Какой тип адресации используется в Excel по умолчанию?
16. В чем состоит удобство применения относительной и абсолютной адресации при заполнении формул?
17. Что такое диапазон, как его выделить?
18. Как защитить содержимое ячеек электронной таблицы от несанкционированного доступа и внести изменения?
19. Укажите, какие вы знаете типы диаграмм, используемых для интерпретации данных электронной таблицы? Поясните, когда следует или не следует использовать каждый из них.
20. Какие способы объединения нескольких исходных электронных таблиц в одну вам известны?
21. Какие особенности печати документов в Excel?
22. Как использовать электронную таблицу для моделирования по типу решения задачи «Что будет, если…».
23. Как выделить смежные и несмежные блоки ячеек?
24. Какие вы знаете команды для работы с базами данных?
25. Что такое консолидация таблиц?
26. Что такое макросы и для чего они используются?
27. Какие вы знаете форматы данных?
28. какие вы знаете типы аргументов функции?
29. Что такое Мастер функции?
30. Что такое Мастер диаграмм?
31. Какие вы знаете метода обработки и анализа данных в Excel?
32. Как осуществляется сортировка списков?
33. Как осуществляется фильтрация списков?
34. В каких случая используют структурирование и группировку данных?
35. Как формируются итоги в списка по заданным критериям?
Литература.
Дата добавления: 2019-02-12 ; просмотров: 828 ; Мы поможем в написании вашей работы!
Источник: studopedia.net
Как определить, что в ячейке находится формула, а не константа без помощи макросов?
Есть ячейка в которую пользователь обязательно должен ввести формулу, а не константу.
Как узнать формула ли в этой ячейке? Способ отсюда:
http://www.excel-vba.ru/chto-umeet-excel/kak-uznat-est-li-formula-v-yachejke/
не подходит ввиду его ограниченности. Как это сделать при помощи VBA я знаю, но мне это не подходит. UPD
Для Excel 2013+ решение найдено:
https://ru.stackoverflow.com/a/444446/481
Теперь интересует- возможно ли это в менее новых версиях Excel?
Отслеживать
задан 14 авг 2015 в 14:15
17.7k 3 3 золотых знака 44 44 серебряных знака 85 85 бронзовых знаков
2 ответа 2
Сортировка: Сброс на вариант по умолчанию
попробовал в Excel2013 все сработало. И так как я сделал:
- Выберем ячейку A1
- Из меню выберем ДАННЫЕ / Работа с Данными / Проверка данных / Проверка данных.
- Параметры / Тип данных / Другой
- В поле формула вводим =ЕФОРМУЛА(A1)
- OK
теперь когда хотим вводит значение дает ошибку. Когда вводим формулу нормально работает.
Отслеживать
ответ дан 21 авг 2015 в 20:52
8,331 4 4 золотых знака 26 26 серебряных знаков 48 48 бронзовых знаков
Спасибо. Ответ верный. У меня excel 2003 -_-
22 авг 2015 в 0:16
– user177221
22 авг 2015 в 6:21
22 авг 2015 в 7:23
22 авг 2015 в 14:18
22 авг 2015 в 14:20
Для версий старше Exel-2013 такого функционала (без использования VBA), к сожалению, нет. Но вариант есть — использование макрофункций.
Не VBA, но макросы должны быть разрешены. На листе не используются, но применимы в именах. С помощью функции ПОЛУЧИТЬ.ЯЧЕЙКУ можно определить содержимое ячейки, ее адрес, заливку.
Ctrl+F3 — мы в Диспетчере_имен. Создать имя (пусть будет «тестформула»), в поле Диапазон вписать формулу:
=ПОЛУЧИТЬ.ЯЧЕЙКУ(6;Лист1!$A$1)
На листе в любой ячейке:
=тестформула
Если в ячейке А1 константа, результат — константа, если формула — отобразится формула. А что такое формула? Текст, который начинается знаком » =»;»формула»;»значение»)
Чем это может помочь при контроле вводимых данных?
Имя у нас уже есть. Доработаем:
Источник: ru.stackoverflow.com
По какому признаку программа определяет что введенные данные являются не значением а формулой
Цель работы: использование логических функций при проведении расчетов.
Задача 1. 1. Подсчитайте количество оценок (отлично, хорошо и т.д.) на основании зачетной ведомости (см. табл. 8).
2. Выполните расчет, используя операцию «Присвоение имени блоку ячеек».
Методика выполнения работы
1. На новом листе рабочей книги создайте таблицу по образцу (см. табл. 8).
2. Заполните данными первую, вторую, третью и четвертую графы.
Фамилия,
имя, отчество
3. В шестую, седьмую, восьмую, девятую и десятую графы введите формулы. Для этого воспользуйтесь Мастером функций панели инструментов Стандартная:
– установите курсор в первую ячейку графы «Кол-во 5» (D2) и активизируйте Мастера функций;
– в первом диалоговом окне выберите категорию функции и название функции:
Категория: Логические функции
Имя функции: ЕСЛИ
– щелкните на кнопке ;
– во втором диалоговом окне установите курсор в поле Логическое выражение и щелкните мышью в рабочей области Ехсеl на ячейке D2 (оценка «5»);
– введите с клавиатуры «= 5»;
– в поле Значение_если_истина введите «1»;
– в поле Значение_если_ложь введите «0»;
– методом протягивания скопируйте формулу в графе «Кол-во 5».
4. Аналогичным способом с помощью Мастера функций введите формулы в графы «Кол-во 4», «Кол-во 3» и т. д., изменяя значение поля Логическое_выражение соответственно на «D2 = = 4», «D2 = 3», «D2 = 2» и «D2 = “н/я”».
5. Чтобы подсчитать сумму всех оценок (пять, четыре и т. д.) и представить результаты в виде отдельной таблицы, нужно по каждой графе «Кол-во оценок» задать имена блокам соответствующих ячеек. Для этого выполните следующие действия:
– выделите блок ячеек Е2 : Е8 графы «Кол-во 5»;
– выполните команду меню Вставка -> Имя —> Присвоить;
– в диалоговом окне Присвоение имени в строке Имя введите слово «Отлично» и щелкните на кнопке Добавить;
– далее выделите ячейки F2 : F8 графы «Кол-во 4» и выполните команду Вставка –> Имя –> Присвоить;
– в диалоговом окне Присвоение имени в строке Имя введите слово «Хорошо»;
– аналогичные действия выполните с остальными графами табл. 8, создав имена блоков ячеек: «Удовлетворительно», «Неудовлетворительно», «Неявка».
6. Создайте таблицу «Итоги сессии» (см. табл. 9).
Итоги сессии
7. Используя имена блоков ячеек, с помощью Мастера функций введите формулу подсчета количества полученных оценок определенного вида:
– установите курсор в ячейку подсчета количества отличных оценок;
– щелкните по кнопке Мастер функций на панели инструментов Стандартная;
– в первом диалоговом окне выберите категорию функции Математические, имя функции Сумм и щелкните на кнопке ;
– во втором диалоговом окне установите курсор в строку Число и введите команду Вставка —> Имя -> Вставить;
– в диалоговом окне Вставка имени выберите имя блока ячеек Отлично и щелкните на кнопке ;
– повторите аналогичные действия для подсчета количества других оценок.
8. Определите количество всех полученных оценок, используя кнопку Автосумма на стандартной панели инструментов.
Задача 2. Определите, в какой из заданных интервалов попадает зарплата каждого сотрудника НИИ, представленная в табл. 10
Методика выполнения работы
1. Создайте новую рабочую книгу.
2. Создайте таблицу из восьми граф, в которой содержатся сведения о семи сотрудниках НИИ: № п/п, Ф.И.О., ежемесячная зарплата (табл. 10).
3. Создайте таблицу, содержащую четыре интервала числовых значений зарплат: 1000–2000, 2000–3000, 3000–4000, 4000–6000 (табл. 11).
4. Чтобы определить, попадает ли значение зарплаты из столбца С в заданный интервал, нужно использовать логическую функцию ЕСЛИ с заданием сложного условия И (табл. 10). Для этого необходимо выполнить следующее:
– установить курсор в ячейку D2;
– щелкнуть на значке Вставка функции стандартной панели инструментов;
– в окне Мастера функций выбрать Категорию функции – Логические, в окне Вид функции — выбрать функцию ЕСЛИ, нажать кнопку ;
– в адресной строке рабочего окна в раскрывающемся списке выбрать функцию И;
– установить курсор в поле Логическое 1;
– в рабочем поле Ехсеl щелкнуть на ячейке С2;
– с клавиатуры ввести символ «>»;
– в рабочем поле Ехсеl щелкнуть на ячейке А10;
– установить курсор в поле Логическое 2;
– в рабочем поле Ехсеl щелкнуть на ячейке С2;
– с клавиатуры ввести символ « < »;
– в рабочем поле Ехсеl щелкнуть на ячейке В10;
– не закрывая окно функции И, щелкнуть на слове «Если» в адресной строке рабочего окна – откроется окно функции ЕСЛИ;
– в поле Значение_если_истина с клавиатуры ввести «1»;
– в поле Значение_если_ложъ с клавиатуры ввести «0»; нажать на кнопку .
Пример выполнения работы
5. Используя операцию автозаполнения, скопируйте в столбец D формулу из ячейки D2, сделайте абсолютными ссылки на ячейки А10 и В10.
6. Аналогичным образом введите формулы в столбцы E, F, G.
7. Для подсчета числа попаданий в каждый интервал выполните следующие действия:
– выделите блок D2 : D6;
– нажмите кнопку Автосумма на стандартной панели инструментов;
– повторите это действие для каждого столбца.
8. Используя операцию Автосумма для значений блоков строк D2 : G2, D3 : G3 и т.д., получите значения столбца Проверка.
9. Значение ячейки Итого столбца Проверка должно совпадать с количеством сотрудников.
Контрольные вопросы
1. Каково назначение электронной таблицы?
2. Как называется документ, созданный в программе Ехсеl? Из чего он состоит?
3. Каковы особенности типового интерфейса табличных процессоров?
4. Какие типы данных могут содержать электронные таблицы?
5. Какие данные называют зависимыми, а какие независимыми?
6. По какому признаку программа определяет, что введенные данные являются не значением, а формулой?
7. Что используется в качестве операндов в формулах в программе Ехсеl?
8. Что такое формула в электронной таблице? Перечислите ее типы. Приведите примеры.
9. Что такое функция в электронной таблице? Перечислите ее типы. Приведите примеры.
10. Поясните, для чего используются абсолютные и относительные адреса ячеек.
11. Что такое автозаполнение?
12. В чем заключается приоритет выполнения операций в арифметических формулах Ехсеl?
13. Как можно «размножить» содержимое ячейки?
14. Как посмотреть и отредактировать формулу, содержащуюся в ячейке?
15. Какой тип адресации используется в Ехсеl по умолчанию?
16. В чем состоит удобство применения относительной и абсолютной адресации при заполнении формул?
17. Что такое диапазон, как его выделить?
18. Как защитить содержимое ячеек электронной таблицы от несанкционированного доступа? Как внести в них изменения?
19. Какие типы диаграмм, используемых для интерпретации данных электронной таблицы, вы знаете? Когда следует или не следует использовать каждый из них?
20. Какие способы объединения нескольких исходных электронных таблиц в одну вам известны?
21. Каковы особенности печати документов в Ехсеl?
22. Как использовать электронную таблицу для моделирования по типу решения задачи «Что будет, если. »?
23. Как выделить смежные и несмежные блоки ячеек?
24. Какие команды для работы с базами данных вы знаете?
25. Что такое консолидация таблиц?
26. Что такое макросы и для чего они используются?
27. Какие форматы данных вы знаете?
28. Какие типы аргументов функции вы знаете?
29. Что такое «Мастер функции»?
30. Что такое «Мастер диаграмм»?
31. Какие методы обработки и анализа данных в Excel вы знаете?
32. Как осуществляется сортировка списков?
33. Как осуществляется фильтрация списков?
34. В каких случаях используют структурирование и группировку данных?
35. Как формируются итоги списков по заданным критериям?
Лабораторная работа 3
Дата: 2019-05-29, просмотров: 342.
Источник: findout.su