Как сделать в excel анкету?

Опросы в Excel, размещенные в Интернете

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

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

В OneDrive для бизнеса

Чтобы приступить к созданию опроса, выполните указанные ниже действия.

Войдите в Office 365 с помощью рабочей или учебной учетной записи.

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

Примечание: Формы для Excel доступна для OneDrive для бизнеса и новых сайтов групп, связанных с группами Office 365. Дополнительные сведения о группах Office 365.

Введите имя опроса, а затем нажмите кнопку создать.

Нажмите кнопку Добавить вопрос , чтобы добавить новый вопрос в опрос. Доступны такие типы вопросов, как Выбор, Текст, Оценка и Дата.

Примечание: Опрос сохраняется автоматически во время его создания.

Для вопросов типа «Выбор» введите текстовое содержание вопроса и каждого из вариантов выбора.

Хотите добавить варианты? Щелкните Добавить вариант, чтобы добавить ответы, отличные от двух стандартных вариантов. Нажмите кнопку «Добавить» , чтобы добавить параметр выбора, отображающий текст. Чтобы удалить вариант, нажмите кнопку корзины рядом с ним. Вы также можете сделать ответ обязательным или разрешить несколько вариантов выбора с помощью параметров под вопросом. Чтобы Microsoft Forms случайно перемешать порядок параметров, отображаемых для пользователей опроса, нажмите кнопку с многоточием (. ), а затем выберите пункт Параметры случайногочисла.

Совет: Нажмите кнопку с многоточием () и щелкните Подзаголовок, чтобы добавить для вопроса подзаголовок.

Для некоторых вопросов будут автоматически выводиться предложения.

Щелкните предлагаемый вариант, чтобы добавить его. В приведенном ниже примере выбраны Понедельник, Среда и Пятница.

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

Совет: Чтобы скопировать вопрос, выделите его и нажмите кнопку Копировать вопрос в правом верхнем углу.

Для текстовых вопросов также можно установить ограничение, если в качестве ответа должны использоваться числа. Нажмите кнопку с многоточием () и щелкните Ограничения. Установить для чисел ограничения можно с помощью параметров Больше, Меньше, Между и множества других.

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

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

Когда вы закончите предварительный просмотр опроса, нажмите кнопку назад .

В OneDrive

Важно: В скором времени опрос Excel будет безотказен. Несмотря на то, что все существующие опросы, созданные в OneDriveс помощью >опроса Excel , будут работать должным образом, используйте Microsoft Forms для всех новых опросов.

В верхней части экрана нажмите Создать, а затем выберите пункт Опрос Excel.

Появится форма, на основе которой можно создать опрос.

Советы для создания опроса Excel

Вы можете добавить опрос в существующую книгу. Когда книга открыта в Excel в Интернете, откройте вкладку Главная и в группе таблицы выберите опрос > новый опрос. В книгу добавится лист опроса.

Заполните поля Введите название и Введите описание. Если вам не нужны название и описание, удалите замещающий текст.

Перетащите вопросы вверх или вниз, чтобы изменить их порядок в форме.

Если вы хотите просмотреть файл в том виде, в котором его увидят получатели, нажмите Сохранить и просмотреть. Чтобы продолжить редактирование, нажмите Изменить опрос. Закончив, нажмите Предоставить доступ к опросу.

Если нажать кнопку Закрыть, вы можете вернуться к редактированию и просмотру формы, перейдя на домашнюю > опрос в Excel в Интернете.

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

Создание эффективной формы для опроса

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

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

Для этого перейдите на вкладку Главная > Опрос > Редактировать опрос и щелкните вопрос, который необходимо изменить. Укажите тип Выбор для параметра Тип отклика и разместите каждый вариант ответа в отдельной строке в поле Варианты выбора.

Вы также можете использовать другие типы ответов.

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

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

Можно также использовать ответы типа Да/Нет, чтобы быстро узнать отношение респондентов к определенному вопросу.

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

Урок 55
Практикум
Автоматизированная обработка данных с помощью анкет

Выполнив практическую работу, вы научитесь:

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

Читать еще:  Как в excel сделать минусы красным?

Постановка задачи — разработка информационной системы для анкетирования

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

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

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

Оформление шаблона анкеты претендента

1. Откройте новый документ Excel или файл-заготовку.

2. Выделите область ячеек А1:J5 и выберите для нее светлую заливку, чтобы в дальнейшем элементы управления были хорошо видны на фоне таблицы.

3. Заполните строки 1-7 таблицы по образцу (рис. 5.8).

Рис. 5.8 Образец оформления шапки задания Конкурс

Сохраните файл в учебной папке под названием Конкурс.

Создание форм оценок, вводимых в анкету членами жюри

1. Отобразите на экране панель инструментов Формы, выбрав в меню команду Вид/Панели инструментов/Формы.

2. Выберите форму Счетчик и прорисуйте ее под первым оцениваемым параметром. Размер формы сделайте примерно 1×2 см. Вид анкеты изображен на рис. 5.10

3. Щелкните на форме правой кнопкой мыши и выберите в контекстном меню команду Формат объекта.

4. В появившемся окне (рис. 5.9) на вкладке Размер установите предел изменения счетчика (исходя из минимального и максимального баллов):

✔ Минимальное значение — 0;
✔ Максимальное значение — 10;
✔ Шаг изменения — 1.

Рис. 5.9. Настройка формы Счетчик

5. На вкладке Свойства установите переключатель привязки объекта к фону в положение Перемещать, но не изменять размеры.

6. Щелчком правой кнопки выделите форму и скопируйте ее.

7. Вставьте копии 8 раз (по количеству оцениваемых параметров), разместив их под соответствующими параметрами, например в ячейках А8:19.

Задание 5.17. Настройка форм оценок

1. Щелкните на первой форме правой кнопкой мыши и выберите в контекстном меню команду Формат объекта.

2. В появившемся окне на вкладке Формат элемента управления щелкните в строке Связь с ячейкой (см. рис. 5.5) и затем на ячейке А12 в таблице. Нажмите кнопку 0К для сохранения настроек.

3. Повторите пункты 1 и 2 для остальных форм, связав их последовательно с ячейками В12:112.

В результате оценок по девяти параметрам в ячейках А12:112, связанных с формами, появится набор значений от О до 10. Это результаты одного претендента.

Организация накопления данных

Задание 5.18. Создание макросов

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

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

Макрос — это программа (набор макрокоманд), которая создается путем записи реальных действий (например, в таблице Excel это выделение ячеек, выбор команд из меню, смена текущего листа и т. д.) при помощи специальных средств для записи макросов или на языке Visual Basic for Applications. При записи макроса сохраняется информация о каждом выполненном шаге в последовательности команд. Записав макрос, его можно запускать всякий раз, когда необходимо выполнить запрограммированную в нем последовательность действий.

Для работы нам необходимо создать три макроса: Накопление_ данных, Очистка и Итоги. Действия, которые следует выполнить для создания макроса Накопление_данных, приведены в табл. 5.2.

Макрос Очистка должен сначала выделять, а затем очищать (клавиша Delete) ячейки D2 и А12:112 на листе 1, готовя их для очередного претендента. Запись макроса проделайте самостоятельно.

Макрос Итоги должен перевести действие с листа 1 на лист 2, ввести в ячейку К5 формулу суммирования результатов одного конкурсанта и скопировать эту формулу в нижестоящие ячейки (количество конкурсантов неизвестно, поэтому задействуйте при копировании формулы 20-30 нижестоящих ячеек). Запись макроса Итоги проделайте самостоятельно. Начните действия с листа 1 и закончите их там же.

Таблица 5.2. Алгооитм создания макроса Накопление данных

Визуализируем результаты опроса с помощью Excel

В школах или компаниях — опросы со скрытыми вопросами для выяснения общей атмосферы и мнений очень популярны. Однозначные ответы (да, нет, множественный выбор или деления шкалы) как раз и дают возможность быстрой обработки через Excel. И для расчета важных контрольных цифр по опросу специальных знаний в области статистики не требуется. Дело в визуализации Картинка может [. ]

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

Дело в визуализации

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

Оно интуитивно передает несколько видов данных одновременно: минимальное и максимальное значение отображается посредством антенн (точечных контактов), а сам блок обозначает область, где собрано 50% данных. Медиана делит блок пополам и своим положением отмечает среднее значение в представлении. Если она находится справа или слева от середины, статистики говорят о распределении с искажением вправо или влево.

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

Читать еще:  Как сделать графики в excel 2016?

Как это сделать: пошаговое руководство

Если данные еще на распознаны, вам понадобится перенести их на лист 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».

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

Нарисовать антенны

Excel-лайфхаки для тех, кто занимается отчётностью и обработкой данных

В этом посте Ренат Шагабутдинов, ассистент генерального директора издательства «Манн, Иванов и Фербер», делится классными Excel-лайфхаками. Приведённые советы будут полезны для всех, кто занимается различной отчётностью, обработкой данных и созданием презентаций.

Ренат уже не в первый раз выступает гостевым автором на Лайфхакере. Ранее мы публиковали отличный материал от него о том, как составить план тренировок: основные книги и онлайн-ресурсы, а также пошаговый алгоритм создания тренировочного плана.

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

Простые альтернативы ВПР и ГПР, если искомые значения не в первом столбце таблицы: ПРОСМОТР, ИНДЕКС+ПОИСКПОЗ

Функции ВПР (VLOOKUP) и ГПР (HLOOKUP) работают только в том случае, если искомые значения находятся в первом столбце или строке той таблицы, из которой вы планируете получить данные.

В остальных случаях есть два варианта:

  1. Использовать функцию ПРОСМОТР (LOOKUP).
    У неё следующий синтаксис: ПРОСМОТР (искомое_значение; вектор_просмотра; вектор_результата). Но для её корректной работы нужно, чтобы значения диапазона вектор_просмотра были отсортированы по возрастанию:
  2. Использовать сочетание функций ПОИСКПОЗ (MATCH) и ИНДЕКС (INDEX).
    Функция ПОИСКПОЗ возвращает порядковый номер элемента в массиве (с её помощью вы можете найти, в какой строке таблицы искомый элемент), а функция ИНДЕКС возвращает элемент массива с заданным номером (который мы и узнаем с помощью функции ПОИСКПОЗ).Синтаксис функций:
    • ПОИСКПОЗ (искомое_значение; массив_поиска; тип_сопоставления) — для нашего случая нам нужен тип сопоставления «точное сопоставление», ему соответствует цифра 0.
    • ИНДЕКС (массив; номер_строки; [номер_столбца]). В данном случае номер столбца указывать не нужно, так как массив состоит из одной строки.

Как быстро заполнить пустые ячейки в списке

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

Выделяем столбец «Тематика», нажимаем на ленте в группе «Главная» кнопку «Найти и выделить» → «Выделить группу ячеек» → «Пустые ячейки» и начинаем ввод формулы (то есть ставим знак равно) и ссылаемся на ячейку сверху, просто нажимая стрелку вверх на клавиатуре. После этого нажимаем Ctrl + Enter. После этого можно сохранить полученные данные как значения, так как формулы больше не нужны:

Как найти ошибки в формуле

Вычисление отдельной части формулы

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

  1. Чтобы вычислить часть формулы прямо в строке формул, выделите эту часть и нажмите F9:

    В данном примере была проблема с функцией ПОИСК (SEARCH) — в ней были перепутаны местами аргументы. Важно помнить, что если вы не отмените вычисление части функции и нажмёте Enter, то вычисленная часть так и останется числом.
  2. Нажмите на кнопку «Вычислить формулу» в группе «Формулы» на ленте:


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

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

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

Появляются стрелки, указывающие, от чего зависит результат вычислений.

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

Щёлкнув на него, мы увидим, где именно находятся влияющие ячейки или диапазоны:

Рядом с кнопкой «Влияющие ячейки» находится кнопка «Зависимые ячейки», работающая аналогично: она отображает стрелки от активной ячейки с формулой к ячейкам, которые зависят от неё.

Кнопка «Убрать стрелки», расположенная в том же блоке, позволяет убрать стрелки к влияющим ячейкам, стрелки к зависимым ячейкам или же оба типа стрелок сразу:

Как найти сумму (количество, среднее) значений ячеек с нескольких листов

Допустим, у вас есть несколько однотипных листов с данными, которые вы хотите сложить, посчитать или обработать как-то иначе:

Для этого в ячейку, в которой вы хотите видеть результат, введите стандартную формулу, например СУММ (SUM), и укажите в аргументе через двоеточие название первого и последнего листов из списка тех листов, что вам нужно обработать:

Вы получите сумму ячеек с адресом B3 с листов «Данные1», «Данные2», «Данные3»:

Такая адресация работает для листов, расположенных последовательно. Синтаксис следующий: =ФУНКЦИЯ (первый_лист:последний_лист!ссылка на диапазон).

Как автоматически строить шаблонные фразы

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

  • Объединяем текст с помощью знака & (можете заменить его функцией СЦЕПИТЬ (CONCATENATE), но в этом нет особого смысла).
  • Текст всегда записывается в кавычках, ссылки на ячейки с текстом — всегда без.
  • Чтобы получить служебный символ «кавычки», используем функцию СИМВОЛ (CHAR) с аргументом 32.

Пример создания шаблонной фразы с помощью формул:

В данном случае, кроме функции СИМВОЛ (CHAR) (для отображения кавычек) используется функция ЕСЛИ (IF), позволяющая изменять текст в зависимости от того, наблюдается ли положительная динамика продаж, и функция ТЕКСТ (TEXT), позволяющая отобразить число в любом формате. Её синтаксис описан ниже:

ТЕКСТ (значение; формат)

Формат указывается в кавычках точно так же, как если бы вы вводили пользовательский формат в окне «Формат ячеек».

Автоматизировать можно и более сложные тексты. В моей практике была автоматизация длинных, но рутинных комментариев к управленческой отчётности в формате «ПОКАЗАТЕЛЬ упал/вырос на XX относительно плана в основном из-за роста/снижения ФАКТОРА1 на XX, роста/снижения ФАКТОРА2 на YY…» с меняющимся списком факторов. Если вы пишете такие комментарии часто и процесс их написания можно алгоритмизировать — стоит один раз озадачиться созданием формулы или макроса, которые избавят вас хотя бы от части работы.

Как сохранить данные в каждой ячейке после объединения

При объединении ячеек сохраняется только одно значение. Excel предупреждает об этом при попытке объединить ячейки:

Соответственно, если у вас была формула, зависящая от каждой ячейки, она перестанет работать после их объединения (ошибка #Н/Д в строках 3–4 примера):

Чтобы объединить ячейки и при этом сохранить данные в каждой из них (возможно, у вас есть формула, как в этом абстрактном примере; возможно, вы хотите объединить ячейки, но сохранить все данные на будущее или скрыть их намеренно), объедините любые ячейки на листе, выделите их, а затем с помощью команды «Формат по образцу» перенесите форматирование на те ячейки, которые вам и нужно объединить:

Как построить сводную из нескольких источников данных

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

Сделать это можно следующим образом: «Файл» → «Параметры» → «Панель быстрого доступа» → «Все команды» → «Мастер сводных таблиц и диаграмм» → «Добавить»:

После этого на ленте появится соответствующая иконка, нажатие на которую вызывает того самого мастера:

При щелчке на неё появляется диалоговое окно:

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

В следующем окне добавьте все диапазоны, на основании которых будет строиться сводная, и задайте им наименования:

После этого в последнем диалоговом окне укажите, где будет размещаться отчёт сводной таблицы — на существующем или новом листе:

Отчёт сводной таблицы готов. В фильтре «Страница 1» вы можете выбрать только один из источников данных, если это необходимо:

Как рассчитать количество вхождений текста A в текст B («МТС тариф СуперМТС» — два вхождения аббревиатуры МТС)

В данном примере в столбце A есть несколько текстовых строк, и наша задача — выяснить, сколько раз в каждой из них встречается искомый текст, расположенный в ячейке E1:

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

  1. ДЛСТР (LEN) — вычисляет длину текста, единственный аргумент — текст. Пример: ДЛСТР (“машина”) = 6.
  2. ПОДСТАВИТЬ (SUBSTITUTE) — заменяет в текстовой строке определённый текст другим. Синтаксис: ПОДСТАВИТЬ (текст; стар_текст; нов_текст). Пример: ПОДСТАВИТЬ (“автомобиль”;“авто”;“”)= “мобиль”.
  3. ПРОПИСН (UPPER) — заменяет все символы в строке на прописные. Единственный аргумент — текст. Пример: ПРОПИСН (“машина”) = “МАШИНА”. Эта функция понадобится нам, чтобы делать поиск без учёта регистра. Ведь ПРОПИСН(“машина”)=ПРОПИСН(“Машина”)

Чтобы найти вхождение определённой текстовой строки в другую, нужно удалить все её вхождения в исходную и сравнить длину полученной строки с исходной:

ДЛСТР(“Тариф МТС Супер МТС”) – ДЛСТР(“Тариф Супер”) = 6

А затем разделить эту разницу на длину той строки, которую мы искали:

6 / ДЛСТР (“МТС”) = 2

Именно два раза строка «МТС» входит в исходную.

Осталось записать этот алгоритм на языке формул (обозначим «текстом» тот текст, в котором мы ищем вхождения, а «искомым» — тот, число вхождений которого нас интересует):

В нашем примере формула выглядит следующим образом:

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