Примеры задач в Excel с информатикой решения

Обновлено: 03.07.2024

1. Введение

Таблицы, несмотря на всю их приземленную прямоугольность, уже несколько десятилетий являются предметом беспокойства и споров. Некоторые авторы предостерегают, что «настоящие программисты не используют электронные таблицы» и что мы должны «остановить эти подрывные электронные таблицы» (Casimir 1992; Chadwick 2003). Другие советуют исследователям, как использовать электронные таблицы для повышения их производительности (Wagner and Keisler 2006). В ходе этих дебатов электронные таблицы продолжают играть важную роль в рабочих процессах исследователей, и становится ясно, что они являются ценным инструментом, от которого исследователи вряд ли полностью откажутся.

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

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

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

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

2. Будьте последовательны

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

Используйте согласованные коды для категориальных переменных. Для категориальной переменной, такой как пол мыши в генетическом исследовании, используйте одно общее значение для самцов (например, «самец») и одно общее значение для женщин (например, «женщина»). Не пишите иногда «М», иногда «мужской», а иногда «мужской». Выберите один и придерживайтесь его.

Используйте согласованный фиксированный код для всех отсутствующих значений. Мы предпочитаем заполнять каждую ячейку, чтобы можно было отличить действительно отсутствующие значения от непреднамеренно отсутствующих значений. Пользователи R предпочитают «NA». Вы также можете использовать дефис. Но придерживайтесь единого значения во всем. Определенно не используйте числовое значение, такое как -999 или 999; легко упустить из виду, что он предназначен для отсутствия. Также не стоит вместо данных вставлять примечание, объясняющее, почему оно отсутствует. Лучше сделайте с такими заметками отдельную колонку.

Используйте согласованные имена переменных. Если в одном файле (например, в первой группе предметов) у вас есть переменная с именем «Глюкоза_10 нед», назовите ее точно так же, как и в других файлах (например, для другие группы предметов). Если он по-разному называется «Глюкоза_10 нед», «глюк_10 нед» и «Глюкоза за 10 недель», то нижестоящему аналитику данных придется выяснить, что на самом деле это одно и то же.

Используйте согласованные идентификаторы субъекта. Если иногда это «153», иногда «мышь153», а иногда «мышь-153F», а иногда «Мышь153», потребуется дополнительная работа, чтобы выяснить кто есть кто.

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

Используйте согласованные имена файлов. Разработайте систему именования файлов. Если один файл называется «Serum_batch1_2015-01-30.csv», то не называйте файл для следующего пакета «batch2_serum_52915.csv», а используйте «Serum_batch2_2015-05-29.csv»."Соблюдение единообразной схемы именования файлов поможет обеспечить правильную организацию файлов и упростит пакетную обработку файлов, если это необходимо.

Используйте в своих заметках согласованные фразы. Если у вас есть отдельный столбец заметок (например, «мертвые» или «вне кривой»), будьте последовательны в том, что вы пишете. Не пишите иногда "мертвый", а иногда "мертвый" или иногда "вне кривой", а иногда "вне кривой ло".

Будьте осторожны с лишними пробелами внутри ячеек. Пустая ячейка отличается от ячейки, содержащей один пробел. А «мужской» отличается от «мужской» (т. е. пробелами в начале и в конце).

3. Выбирайте хорошие названия для вещей

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

Как правило, не используйте пробелы ни в именах переменных, ни в именах файлов. Они усложняют программирование: аналитику нужно будет заключать все в двойные кавычки, например «глюкоза 6 недель», а не просто писать «глюкоза_6_недель». Там, где вы можете использовать пробелы, используйте символы подчеркивания или, возможно, дефисы. Но не используйте смесь подчеркиваний и дефисов; выберите один и будьте последовательны.

Остерегайтесь лишних пробелов в начале или конце имени переменной. «глюкоза» отличается от «глюкозы» (лишним пробелом в конце).

Объединение результатов, полученных в результате реакции между бромом и цис- и транс-изомерами 1,2-дифенилэтиленов, позволяет учащимся открыть механизм антиприсоединения, характерный для большинства ситуаций. Расширение этого эксперимента за счет включения 1,1-дифенилэтилена дает учащимся возможность открыть реакцию электрофильного замещения. Это служит отличным трамплином. [Показать полную аннотацию] для последующих экспериментов и обсуждения электрофильного ароматического замещения. Ключевые слова (аудитория): студенты второго курса бакалавриата

Введение

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

Новые взгляды на электронную коммерцию

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

Автоматизированное измерение и анализ эффективности обучения выбранным темам Excel во введении.

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

Пользователи Excel давно и успешно применяют эту программу для решения различного рода задач в разных областях.

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

Решение проблем с оптимизацией данных

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

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

Менеджер сценариев («ДАННЫЕ» — «Анализ «что, если»» — «Менеджер сценариев») анализирует несколько вариантов исходных значений, создает и оценивает наборы сценариев.
Подбор параметров («ДАННЫЕ» — «Анализ «что, если»» — «Поиск цели») помогает найти значения, обеспечивающие желаемый результат.
Поиск решения (надстройка Microsoft Excel, "ДАННЫЕ" - "Поиск решения") вычисляет оптимальное значение с учетом переменных и ограничений. Перейдите по ссылке и узнайте, как подключить параметр «Поиск решения».

Команда "Подбор параметров" используется для решения простейших задач. Вы используете «Менеджер сценариев» для самых сложных задач. Рассмотрим пример решения оптимизационной задачи с помощью модуля "Решатель".

Условие. Компания производит несколько марок йогурта. Назовем их «1», «2» и «3». Предприятие получает 200 рублей, продав 100 банок йогурта "1". Марка «2» принесла 250 руб. «3» - 300 руб. Продажи хорошо налажены, но количество доступного сырья ограничено. Вам нужно выяснить, какой йогурт и в каком количестве вам нужно сделать, чтобы получить максимальный доход от продаж.

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

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

Мы до сих пор не знаем количество продуктов. Это переменные.
В столбце "Прибыль" записаны следующие формулы: С11:= 200*В11, С12:= 250*В12, С13:= 300*В13, С14: =СУММ(С11:С13).
Потребление сырья ограничено (это ограничения). В ячейки добавляются следующие формулы:
молоко А18: кефир В18: яйцо С18: сахар D18: То есть мы умножили норму расхода на количество.
Цель состоит в том, чтобы найти максимально возможную прибыль. Это ячейка C14.

Активируйте команду "Решатель" и введите параметры.

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

Лучший вариант — сконцентрироваться на приготовлении йогуртов «3» и «1». Кефир "2" производить не стоит.

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

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

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

Сделаем таблицу с исходными данными:

Поскольку процентная ставка не меняется в течение всего периода, мы используем функцию =PV (Ставка, Кпер, Pmt, Fv, Тип) к ячейке B7.

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

Ставка. Процентная ставка составляет 20 %/4, поскольку проценты начисляются ежеквартально.
Кпер — общее количество периодов равно 4*4 (общий срок вклада*количество начисляемых периодов в год).
Pmt — Рекуррентные платежи равны 0. Мы ничего не пишем. Депозит не пополняется.
Fv — будущая стоимость — это сумма денег, которую мы хотим получить в конце периода депозита.
Тип – 0.

Инвестор должен внести эти деньги, чтобы результат был отрицательным.

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

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

Для установления количественных и качественных взаимосвязей используются математические и статистические методы и модели.

Есть два диапазона значений:

Значения X будут играть роль факторной характеристики, Y – результирующей. Задача состоит в том, чтобы найти коэффициент корреляции.

Для решения этой задачи предусмотрена функция КОРРЕЛ (массив 1, массив 2).

Решение логических задач в Excel

Табличный процессор имеет встроенные логические функции. Любой из них должен содержать хотя бы один оператор сравнения, который будет определять отношение между элементами (=,>, =, например, Задание. Студенты сдали тест. Каждый из них получил оценку. Тест сдается, если есть более 4 баллов. Если меньше - не сдается.

Поместите курсор в ячейку C1. Нажмите значок функции. Выберите "ЕСЛИ".
Заполните аргументы. Логическим выражением является B1> = 4. Это условие, при котором логическое значение равно TRUE.
Если TRUE - "Тест пройден". FALSE — "Тест не пройден".

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

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

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

Составляем таблицу со значениями матрицы A.
Выделяем на том же листе область для обратной матрицы.
Перейдите в раздел "ФОРМУЛЫ". Категория - "Математика и триггер". Тип - "МИНВЕРС".
В поле аргумента «Массив» введите диапазон матрицы A.
Одновременно нажмите Shift + Ctrl + Enter, потому что это обязательно для ввода массивов.

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

Профессор Матич — новый преподаватель IUB, не знакомый с буквенной системой оценивания. Он не умеет переводить оценки из процентов в буквы. Школа информатики нанимает студентов I101 для разработки окончательного приложения для преобразования процентного балла в буквенные оценки.

Вам необходимо отредактировать электронную таблицу Excel (i) с алгоритмом преобразования процентных оценок в буквенные оценки и (ii) создать другой алгоритм для выявления учащихся с оценками выше среднего. Вам также необходимо (iii) рассчитать распределение вероятностей для каждой буквенной оценки и (iv) создать блок-схему для алгоритма (i) и опубликовать ее в своем блоге.

Наконец, вам нужно (v) сравнить энтропии Шеннона трех профессоров: Матика, Черчилля и Муссолини. Вы найдете информацию об этих профессорах во второй книге электронной таблицы. Там представлено распределение вероятностей оценок для трех профессоров. Вы должны кратко прокомментировать свои результаты в электронной таблице и (vi) ответить на следующие два вопроса: Вопрос 1: В классе какого профессора (Матик, Черчил и Муссолини) окончательная оценка более неопределенна? Какой из них более надежен? Вопрос 2: Помогает ли энтропия Шеннона решить, какой из трех курсов выбрать? Как?

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

ПРИМЕЧАНИЕ. В прилагаемой таблице Excel есть две книги, вам понадобятся обе

Возвращает одно значение, если указанное вами условие оценивается как ИСТИНА, и другое значение, если оно оценивается как ЛОЖЬ.

Используйте ЕСЛИ для проведения условных проверок значений и формул.

ЕСЛИ(логическая_проверка,значение_если_истина,значение_если_ложь)

Логическая_проверка – это любое значение или выражение, которое может быть оценено как ИСТИНА или ЛОЖЬ. Например, A10=100 — логическое выражение; если значение в ячейке A10 равно 100, выражение оценивается как ИСТИНА. В противном случае выражение оценивается как FALSE.

Value_if_true — это значение, которое возвращается, если logical_test имеет значение TRUE. Например, если этот аргумент представляет собой текстовую строку «В рамках бюджета», а аргумент логическая_проверка имеет значение ИСТИНА, то функция ЕСЛИ отображает текст «В рамках бюджета». Если лог_выражение равно ИСТИНА, а значение_если_истина пусто, этот аргумент возвращает 0 (ноль). Чтобы отобразить слово ИСТИНА, используйте логическое значение ИСТИНА для этого аргумента. Value_if_true может быть другой формулой.

Value_if_false — это значение, которое возвращается, если logical_test имеет значение FALSE. Например, если этот аргумент представляет собой текстовую строку «Превышение бюджета», а аргумент логическая_проверка оценивается как ЛОЖЬ, то функция ЕСЛИ отображает текст «Превышение бюджета». Если лог_выражение равно ЛОЖЬ, а значение_если_ложь опущено (то есть после значения_если_истина запятая отсутствует), то возвращается логическое значение ЛОЖЬ. Если логическая_проверка равна ЛОЖЬ, а значение_если_ложь пусто (то есть после значения_если_истина стоит запятая, за которой следует закрывающая скобка), то возвращается значение 0 (ноль). Value_if_false может быть другой формулой.

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