Как обработать опрос в excel
С помощью средств анализа "что если" в Excel вы можете экспериментировать с различными наборами значений в одной или нескольких формулах, чтобы изучить все возможные результаты.
Например, можно выполнить анализ "что если" для формирования двух бюджетов с разными предполагаемыми уровнями дохода. Или можно указать нужный результат формулы, а затем определить, какие наборы значений позволят его получить. В Excel предлагается несколько средств для выполнения разных типов анализа.
Обратите внимание на то, что в этой статье приведен только обзор инструментов. Подробные сведения о каждом из них можно найти по ссылкам ниже.
Анализ "что если" — это процесс изменения значений в ячейках, который позволяет увидеть, как эти изменения влияют на результаты формул на листе.
В Excel предлагаются средства анализа "что если" трех типов: сценарии, таблицы данных и подбор параметров. В сценариях и таблицах данных берутся наборы входных значений и определяются возможные результаты. Таблицы данных работают только с одной или двумя переменными, но могут принимать множество различных значений для них. Сценарий может содержать несколько переменных, но допускает не более 32 значений. Подбор параметров отличается от сценариев и таблиц данных: при его использовании берется результат и определяются возможные входные значения для его получения.
Помимо этих трех средств можно установить надстройки для выполнения анализа "что если", например надстройку Поиск решения. Эта надстройка похожа на подбор параметров, но позволяет использовать больше переменных. Вы также можете создавать прогнозы, используя маркер заполнения и различные команды, встроенные в Excel.
Для более сложных моделей можно использовать надстройку Пакет анализа.
Использование сценариев для учета множества разных переменныхСценарий — это набор значений, которые сохраняются в Excel и могут автоматически подставляться в ячейки на листе. Вы можете создавать и сохранять различные группы значений на листе, а затем переключиться на любой из этих новых сценариев, чтобы просмотреть другие результаты.
Предположим, у вас есть два сценария бюджета: для худшего и лучшего случаев. Вы можете с помощью диспетчера сценариев создать оба сценария на одном листе, а затем переключаться между ними. Для каждого сценария вы указываете изменяемые ячейки и значения, которые нужно использовать. При переключении между сценариями результат в ячейках изменяется, отражая различные значения изменяемых ячеек.
1. Изменяемые ячейки
2. Ячейка результата
1. Изменяемые ячейки
2. Ячейка результата
Если у нескольких человек есть конкретные данные в отдельных книгах, которые вы хотите использовать в сценариях, вы можете собрать эти книги и объединить их сценарии.
После создания или сбора всех нужных сценариев вы можете создать сводный отчет по сценариям, в который включаются данные из этих сценариев. В отчете по сценариям все данные отображаются в одной таблице на новом листе.
Примечание: В отчетах по сценариям автоматический пересчет не выполняется. Изменения значений в сценарии не будут отражается в уже существующем сводном отчете. Вам потребуется создать новый сводный отчет.
Использование подбора параметров для получения нужного результатаЕсли вы знаете нужный результат формулы, но не знаете, какое входные значения требуется для получения этого результата, используйте функцию "Поиск окна". Предположим, что вам нужно занять денег. Вы знаете, сколько вам нужно, на какой срок и сколько вы сможете выплачивать каждый месяц. С помощью средства подбора параметров вы можете определить, какая процентная ставка вам подойдет.
Ячейки B1, B2 и B3 — это значения для суммы займа, длины срока и процентной ставки.
Ячейка B4 отображает результат формулы =PMT(B3/12;B2;B1).
Примечание: В средстве поиска окна можно ввести только одно значение переменной. Если вы хотите определить несколько входных значений, например сумму займа и сумму ежемесячного платежа по кредиту, используйте надстройка "Надстройка "Найти решение". Дополнительные сведения о надстройки "Решение" см. в разделе Подготовка прогнозов и расширенных бизнес-моделей ипо ссылкам в разделе См. также.
Использование таблиц данных для просмотра влияния переменных в формулеЕсли у вас есть формула с одной или двумя переменными либо несколько формул, в которых используется одна общая переменная, вы можете просмотреть все результаты в одной таблице данных. С помощью таблиц данных можно легко и быстро проверить несколько возможностей. Поскольку используются всего одна или две переменные, результат можно без труда прочитать или опубликовать в табличной форме. Если для книги включен автоматический пересчет, данные в таблицах данных сразу же пересчитываются, и вы всегда видите свежие данные.
Ячейка B3 содержит входные значения.
Ячейки C3, C4 и C5 являются значениями, Excel заменяются на основе значения, введенного в ячейку B3.
В таблицу данных нельзя помещать больше двух переменных. Для анализа большего количества переменных используйте сценарии. Несмотря на то что переменных не может быть больше двух, можно использовать сколько угодно различных значений переменных. В сценарии можно использовать не более 32 различных значений, зато вы можете создать сколько угодно сценариев.
При подготовке прогнозов вы можете использовать Excel для автоматической генерации будущих значений на базе существующих данных или для автоматического вычисления экстраполированных значений на основе арифметической или геометрической прогрессии.
Вы можете заполнить ряд значений, которые соответствуют простому линейному или экспоненциальному тренду роста, с помощью ручки заполнения или команды Ряд. Для расширения сложных и нелинейных данных можно использовать функции или средство регрессионного анализа надстройки "Надстройка анализа".
В средстве подбора параметров можно использовать только одну переменную, а с помощью надстройки Поиск решения вы можете создать обратную проекцию для большего количества переменных. Надстройка "Поиск решения" помогает найти оптимальное значение для формулы в одной ячейке листа, которая называется целевой.
Над решением работает группа ячеек, связанных с формулой в целевой ячейке. "Решение" изменяет значения изменяемых ячеек, которые вы указываете (регулируемые ячейки), чтобы получить результат, который вы указываете из формулы целевой ячейки. Ограничения можно применять для ограничения значений, которые можно использовать в модели, а ограничения могут ссылаться на другие ячейки, влияющие на формулу целевой ячейки.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Примечание: В настоящее время мы обновляем эту функцию и развертываем изменения, поэтому у вас могут быть другие возможности, чем описано ниже. Узнайте больше о предстоящих улучшениях функциональности в области "Создание формы" с помощью Microsoft Forms.
Опросы позволяют другим людям заполнять ваш список, например список участников или анкету, где вы можете увидеть все это в одном месте в Интернете. Вот как можно создать опрос в OneDrive и OneDrive для работы или учебы:
В OneDrive для работы или учебы
Чтобы при начать создание опроса, выполните указанные здесь действия.
Войдите в Microsoft 365 с помощью рабочей или учебной учетной записи.
Примечание: Формы для Excel доступны для OneDrive для работы или учебы и новых сайтов групп, связанных с Microsoft 365 группами. Узнайте больше о группах Microsoft 365.
Введите имя опроса и нажмите кнопку "Создать".
Примечание: Опрос автоматически сохраняются при его создании.
Для вопросов типа "Выбор" введите текстовое содержание вопроса и каждого из вариантов выбора.
Для некоторых вопросов будут автоматически выводиться предложения.
Щелкните предлагаемый вариант, чтобы добавить его. В приведенном ниже примере выбраны Понедельник, Среда и Пятница.
Совет: Чтобы скопировать вопрос, выберите его и нажмите кнопку "Копировать вопрос в правом верхнем углу.
Щелкните "Мобильный", чтобы посмотреть, как ваш опрос будет выглядеть на мобильном устройстве.
В OneDrive
Важно: В ближайшее время будет отменена программа опроса Excel. Хотя существующие опросы, созданные в OneDrive с помощью > Excel, будут работать, но при создании опросов используйте Microsoft Forms.
В верхней части экрана нажмите Создать, а затем выберите пункт Опрос Excel.
Появится форма, на основе которой можно создать опрос.
Советы для создания опроса Excel
Вы можете добавить опрос в существующую книгу. Открыв книгу в Excel в Интернете, перейдите на главная и в группе "Таблицы" щелкните "Опрос > "Новый опрос". В книгу добавится лист опроса.
Заполните поля Введите название и Введите описание. Если вам не нужны название и описание, удалите замещающий текст.
Перетащите вопросы вверх или вниз, чтобы изменить их порядок в форме.
Если вы хотите просмотреть файл в том виде, в котором его увидят получатели, нажмите Сохранить и просмотреть. Чтобы продолжить редактирование, нажмите Изменить опрос. Закончив, нажмите Предоставить доступ к опросу.
Если нажать кнопку "Закрыть",вернуться к редактированию и просмотру формы можно на домашней > вExcel в Интернете.
Создание эффективной формы для опроса
Добавляя вопросы в форму, помните, что каждый из них соответствует столбцу на листе Excel.
Для этого перейдите на вкладку Главная > Опрос > Редактировать опрос и щелкните вопрос, который необходимо изменить. Укажите тип Выбор для параметра Тип отклика и разместите каждый вариант ответа в отдельной строке в поле Варианты выбора.
Типы Дата или Время позволяют сортировать результаты в хронологическом порядке.
Можно также использовать ответы типа Да/Нет, чтобы быстро узнать отношение респондентов к определенному вопросу.
Примечание: По мере того как вы добавляете вопросы в форму опроса, в электронной таблице создаются столбцы. Изменения, внесенные вами в форму опроса, отражаются в электронной таблице, кроме случаев, когда вы удалили вопрос или изменили порядок вопросов в форме. В этих ситуациях вам придется обновить таблицу вручную: удалите столбцы, которые соответствуют удаленным вопросам, или измените порядок столбцов с помощью вырезания и вставки.
В школах или компаниях — опросы со скрытыми вопросами для выяснения общей атмосферы и мнений очень популярны. Однозначные ответы (да, нет, множественный выбор или деления шкалы) как раз и дают возможность быстрой обработки через Excel. И для расчета важных контрольных цифр по опросу специальных знаний в области статистики не требуется. Дело в визуализации Картинка может [. ]
В школах или компаниях — опросы со скрытыми вопросами для выяснения общей атмосферы и мнений очень популярны. Однозначные ответы (да, нет, множественный выбор или деления шкалы) как раз и дают возможность быстрой обработки через Excel. И для расчета важных контрольных цифр по опросу специальных знаний в области статистики не требуется.
Дело в визуализации
Картинка может быть красноречивее слов, а умело подобранная диаграмма в одно мгновение передает результат опроса. В то время как линейчатая или круговая диаграммы являются удачным выбором для решений типа «да-нет», для опросов, при которых значения рассчитываются по фиксированной шкале, в качестве замечательной основы предлагается блочное графическое представление.
Оно интуитивно передает несколько видов данных одновременно: минимальное и максимальное значение отображается посредством антенн (точечных контактов), а сам блок обозначает область, где собрано 50% данных. Медиана делит блок пополам и своим положением отмечает среднее значение в представлении. Если она находится справа или слева от середины, статистики говорят о распределении с искажением вправо или влево.
Если при обработке опроса блок имеет вытянутую форму, а антенны или точечные контакты достигают соответствующих концов опросной шкалы, к тому же становится ясно, что решение у этого вопроса отнюдь не однозначное — результаты в таком случае распределяются по широкому спектру. Короткий блок с короткими антеннами, напротив, показывает концентрацию на определенной области шкалы.
Как это сделать: пошаговое руководство
Если данные еще на распознаны, вам понадобится перенести их на лист Excel. Если, как в нашем примере, идет шкала от 1 до 6 (оценочные баллы), то и задаваться должны только эти значения. Если у вас в списке присутствуют нулевые значения, например, для недействительных или отсутствующих записей, их следует удалить.
Собрать данные
2. Рассчитать максимум и минимум
В первую очередь вам потребуется найти максимальное и минимальное значение числового ряда. Для этого используется следующая синтаксическая конструкция: «=МАКС(B8:JF8)» и, соответственно, «=МИН(B8:JF8)», причем диапазон данных вам, конечно же, придется настраивать под ваш проект.
Рассчитать максимум и минимум
3. Рассчитать медиану
Медиана обозначает среднее значение распределения или второй квартиль. Синтаксическая конструкция имеет вид «=медиана (B8:JF8)».
Рассчитать медиану
4. Определить оставшиеся квартили
Теперь нам еще понадобятся первый и третий квартили, чтобы можно было рассчитать блочные диаграммы. Синтаксическая конструкция: «=квартиль (B8:JF8;1)» и «=квартиль (B8:JF8;3)».
Определить оставшиеся квартили
5. Рассчитать вспомогательные величины
Поскольку при построении блочной диаграммы речь идет о дополнительном представлении значений, нам еще потребуется несколько разностей в качестве вспомогательных величин: H1=минимум; H2=1-й квартиль-минимум; H3=медиана-1-й квартиль: H4=3-й квартиль-медиана; H5=максимум-3-й квартиль.
Рассчитать вспомогательные величины
6. Начертить линейчатую диаграмму
Если вы рассчитали вспомогательные величины, как показано на иллюстрации, выделите небольшую таблицу, но без строки с пятой вспомогательной величиной. Перейдите в меню «Вставка | Диаграммы | Линейчатая | Линейчатая с накоплением». Теперь у вас отображается четыре одноцветных полосы. Нажмите на кнопку «Изменить строку | Cтолбец».
Начертить линейчатую диаграмму
7. Откорректировать диаграмму
Теперь выделите самый левый столбец диаграммы и с помощью контекстного меню перейдите в пункт «Формат ряда данных». Отключите пункты «Заливка» и «Цвет контура». То же самое проделайте с крайним правым столбцом. Оставьте левый столбец выделенным.
Откорректировать диаграмму
8. Нарисовать антенны
В строке меню перейдите к пункту «Работа с диаграммами | Макет | Планки погрешностей | Дополнительные параметры планок погрешностей…». Установите здесь направление на «Минус», а относительное значение на «100» (величина погрешности). После этого закройте меню настроек.
Теперь выделите крайний правый сегмент. Снова перейдите к контекстному меню для планок погрешностей. На этот раз установите направление на «Плюс», а в разделе «Величина погрешности» под пунктом «Пользовательская» задайте положительное значение погрешности «Вспомогательные данные 5».
Для этого вам понадобится просто кликнуть кнопкой мыши в таблице со вспомогательными величинами. Теперь, в завершение создания диаграммы, вы можете отключить легенду и линии координатной сетки, и у вас готово отличное блочное графическое представление для визуализации небольшого опроса.
Нарисовать антенны
Проверка данных позволяет ограничить тип данных или значения, которые можно ввести в ячейку. Чаще всего она используется для создания раскрывающихся списков.
Проверьте, как это работает!
Выделите ячейки, для которых необходимо создать правило.
Выберите Данные > Проверка данных.
На вкладке Параметры в списке Тип данных выберите подходящий вариант:
Целое число, чтобы можно было ввести только целое число.
Десятичное число, чтобы можно было ввести только десятичное число.
Список, чтобы данные выбирались из раскрывающегося списка.
Дата, чтобы можно было ввести только дату.
Время, чтобы можно было ввести только время.
Длина текста, чтобы ограничить длину текста.
Другой, чтобы задать настраиваемую формулу.
В списке Значение выберите условие.
Задайте остальные обязательные значения с учетом параметров Тип данных и Значение.
Нажмите ОК.
Скачивание примеров
Ограничение ввода данных
Выделите ячейки, для которых нужно ограничить ввод данных.
На вкладке Данные щелкните Проверка данных > Проверка данных.
Примечание: Если команда проверки недоступна, возможно, лист защищен или книга является общей. Если книга является общей или лист защищен, изменить параметры проверки данных невозможно. Дополнительные сведения о защите книги см. в статье Защита книги.
В поле Тип данных выберите тип данных, который нужно разрешить, и заполните ограничивающие условия и значения.
Примечание: Поля, в которых вводятся ограничивающие значения, помечаются на основе выбранных вами данных и ограничивающих условий. Например, если выбран тип данных "Дата", вы сможете вводить ограничения в полях минимального и максимального значения с пометкой Начальная дата и Конечная дата.
Запрос для пользователей на ввод допустимых значений
Выделите ячейки, в которых для пользователей нужно отображать запрос на ввод допустимых данных.
На вкладке Данные щелкните Проверка данных > Проверка данных.
Примечание: Если команда проверки недоступна, возможно, лист защищен или книга является общей. Если книга является общей или лист защищен, изменить параметры проверки данных невозможно. Дополнительные сведения о защите книги см. в статье Защита книги.
На вкладке Подсказка по вводу установите флажок Отображать подсказку, если ячейка является текущей.
На вкладке Данные щелкните Проверка данных > Проверка данных.
Примечание: Если команда проверки недоступна, возможно, лист защищен или книга является общей. Если книга является общей или лист защищен, изменить параметры проверки данных невозможно. Дополнительные сведения о защите книги см. в статье Защита книги.
Выполните одно из следующих действий.
В контекстном меню Вид выберите
Требовать от пользователей исправления ошибки перед продолжением
Предупреждать пользователей о том, что данные недопустимы, и требовать от них выбора варианта Да или Нет, чтобы указать, нужно ли продолжать
Предупреждение
Добавление проверки данных в ячейку или диапазон ячеек
Примечание: Первые два действия, указанные в этом разделе, можно использовать для добавления любого типа проверки данных. Действия 3–7 относятся к созданию раскрывающегося списка.
Выделите одну или несколько ячеек, к которым нужно применить проверку.
На вкладке Данные в группе Работа с данными нажмите кнопку Проверка данных.
На вкладке Параметры в поле Разрешить выберите Список.
В поле Источник введите значения списка, разделенные запятыми. Например, введите Низкий,Средний,Высокий.
Убедитесь, что установлен флажок Список допустимых значений. В противном случае рядом с ячейкой не будет отображена стрелка раскрывающегося списка.
Чтобы указать, как обрабатывать пустые (нулевые) значения, установите или снимите флажок Игнорировать пустые ячейки.
После создания раскрывающегося списка убедитесь, что он работает так, как нужно. Например, можно проверить, достаточно ли ширины ячеек для отображения всех ваших записей.
Отмена проверки данных. Выделите ячейки, проверку которых вы хотите отменить, щелкните Данные > Проверка данных и в диалоговом окне проверки данных нажмите кнопки Очистить все и ОК.
В таблице перечислены другие типы проверки данных и указано, как применить их к данным на листе.
Разрешить вводить только целые числа из определенного диапазона
Выполните действия 1–2, указанные выше.
В списке Разрешить выберите значение Целое число.
В поле Данные выберите необходимый тип ограничения. Например, для задания верхнего и нижнего пределов выберите ограничение Диапазон.
Введите минимальное, максимальное или определенное разрешенное значение.
Можно также ввести формулу, которая возвращает числовое значение.
Например, допустим, что вы проверяете значения в ячейке F1. Чтобы задать минимальный объем вычетов, равный значению этой ячейки, умноженному на 2, выберите пункт Больше или равно в поле Данные и введите формулу =2*F1 в поле Минимальное значение.
Разрешить вводить только десятичные числа из определенного диапазона
Выполните действия 1–2, указанные выше.
В поле Разрешить выберите значение Десятичный.
В поле Данные выберите необходимый тип ограничения. Например, для задания верхнего и нижнего пределов выберите ограничение Диапазон.
Введите минимальное, максимальное или определенное разрешенное значение.
Можно также ввести формулу, которая возвращает числовое значение. Например, для задания максимального значения комиссионных и премиальных в размере 6% от заработной платы продавца в ячейке E1 выберите пункт Меньше или равно в поле Данные и введите формулу =E1*6% в поле Максимальное значение.
Примечание: Чтобы пользователи могли вводить проценты, например "20 %", в поле Разрешить выберите значение Десятичное число, в поле Данные задайте необходимый тип ограничения, введите минимальное, максимальное или определенное значение в виде десятичного числа, например 0,2, а затем отобразите ячейку проверки данных в виде процентного значения, выделив ее и нажав кнопку Процентный формат на вкладке Главная в группе Число.
Разрешить вводить только даты в заданном интервале времени
Выполните действия 1–2, указанные выше.
В поле Разрешить выберите значение Дата.
В поле Данные выберите необходимый тип ограничения. Например, для разрешения даты после определенного дня выберите ограничение Больше.
Введите начальную, конечную или определенную разрешенную дату.
Вы также можете ввести формулу, которая возвращает дату. Например, чтобы задать интервал времени между текущей датой и датой через 3 дня после текущей, выберите пункт Между в поле Данные, потом введите =СЕГОДНЯ() в поле Дата начала и затем введите =СЕГОДНЯ()+3 в поле Дата завершения.
Разрешить вводить только время в заданном интервале
Выполните действия 1–2, указанные выше.
В поле Разрешить выберите значение Время.
В поле Данные выберите необходимый тип ограничения. Например, для разрешения времени до определенного времени дня выберите ограничение меньше.
Укажите время начала, окончания или определенное время, которое необходимо разрешить. Если вы хотите ввести точное время, используйте формат чч:мм.
Например, если в ячейке E2 задано время начала (8:00), а в ячейке F2 — время окончания (17:00) и вы хотите ограничить собрания этим промежутком, выберите между в поле Данные, а затем введите =E2 в поле Время начала и =F2 в поле Время окончания.
Разрешить вводить только текст определенной длины
Выполните действия 1–2, указанные выше.
В поле Разрешить выберите значение Длина текста.
В поле Данные выберите необходимый тип ограничения. Например, для установки определенного количества знаков выберите ограничение Меньше или равно.
В данном случае нам нужно ограничить длину вводимого текста 25 символами, поэтому выберем меньше или равно в поле Данные и введем 25 в поле Максимальное значение.
Вычислять допустимое значение на основе содержимого другой ячейки
Выполните действия 1–2, указанные выше.
В поле Разрешить выберите необходимый тип данных.
В поле Данные выберите необходимый тип ограничения.
В поле или полях, расположенных под полем Данные, выберите ячейку, которую необходимо использовать для определения допустимых значений.
Например, чтобы допустить ввод сведений для счета только тогда, когда итог не превышает бюджет в ячейке E1, выберите значение Число десятичных знаков в списке Разрешить, ограничение "Меньше или равно" в списке "Данные", а в поле Максимальное значение введите >= =E1.
В примерах ниже при создании формул с условиями используется настраиваемый вариант. В этом случае содержимое поля "Данные" не играет роли.
Представленные в этой статье снимки экрана созданы в Excel 2016, но функции аналогичны Excel в Интернете.
Введите формулу
Значение в ячейке, содержащей код продукта (C2), всегда начинается со стандартного префикса "ID-" и имеет длину не менее 10 (более 9) знаков.
Ячейка с наименованием продукта (D2) содержала только текст.
Значение в ячейке, содержащей чью-то дату рождения (B6), было больше числа лет, указанного в ячейке B4.
=ЕСЛИ(B6<=(СЕГОДНЯ()-(365*B4));TRUE,FALSE)
Все данные в диапазоне ячеек A2:A10 содержали уникальные значения.
=СЧЁТЕСЛИ($A$2:$A$10;A2)=1
Примечание: Необходимо сначала ввести формулу проверки данных в ячейку A2, а затем скопировать эту ячейку в ячейки A3:A10 так, чтобы второй аргумент СЧЁТЕСЛИ соответствовал текущей ячейке. Часть A2)=1 изменится на A3)=1, A4)=1 и т. д.
Читайте также: