Как в эксель сделать выборку по датам
Доброго времени суток господа форумчане!
Обращаюсь к Вам с насущной проблемой.
Суть такова:
Имеется рабочая книга, на текущий момент состоящая из 3 (трех) листов.
На 1 (первом) листе располагается таблица получающая внешние данные из текста (csv-файл) с периодичностью обновления в 1 минуту, состоящая из n-ного числа строк и 7 (семи) столбцов.
На данный момент столкнулась с проблемой выборки значений с листа 1 (один) на лист 2 (два) по двум одновременным условиям диапазона даты и времени (столбцы А и B).
При помощи форумов пришла к тому, что в идеале такая задача решается с помощью макросов.
На данный момент удалось организовать выборку и копирование результата только по дате , с использованием кпопки (см. вложение)
А в идеале хотелось бы, чтобы при заходе в файл появлялась ActiveForm, в которую можно было бы ввести начальные дату и время и конечные дату и время (по которым и будет происходить выборка) и выбрать один из двух листов (4 и 5) с результатом (планируется два вида отчетности на основе полученной выборки с Листа 2, на листы 4 и 5).
Немного сумбурно получилось, но готова ответить на все вопросы. Отмечу, что в макросах я пока где-то на уровне 0, но старательно учусь.
Замечу, что я не прошу предоставить готовое решение, но если такое есть, то готова отблагодарить благодетеля.
Заранее спасибо.
UPD: Прошу прощения, не сразу заметила, что не прикрепился файл
Доброго времени суток господа форумчане!
Обращаюсь к Вам с насущной проблемой.
Суть такова:
Имеется рабочая книга, на текущий момент состоящая из 3 (трех) листов.
На 1 (первом) листе располагается таблица получающая внешние данные из текста (csv-файл) с периодичностью обновления в 1 минуту, состоящая из n-ного числа строк и 7 (семи) столбцов.
На данный момент столкнулась с проблемой выборки значений с листа 1 (один) на лист 2 (два) по двум одновременным условиям диапазона даты и времени (столбцы А и B).
При помощи форумов пришла к тому, что в идеале такая задача решается с помощью макросов.
На данный момент удалось организовать выборку и копирование результата только по дате , с использованием кпопки (см. вложение)
А в идеале хотелось бы, чтобы при заходе в файл появлялась ActiveForm, в которую можно было бы ввести начальные дату и время и конечные дату и время (по которым и будет происходить выборка) и выбрать один из двух листов (4 и 5) с результатом (планируется два вида отчетности на основе полученной выборки с Листа 2, на листы 4 и 5).
Немного сумбурно получилось, но готова ответить на все вопросы. Отмечу, что в макросах я пока где-то на уровне 0, но старательно учусь.
Замечу, что я не прошу предоставить готовое решение, но если такое есть, то готова отблагодарить благодетеля.
Заранее спасибо.
UPD: Прошу прощения, не сразу заметила, что не прикрепился файл Jerdas
Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.
При создании формул массива, операторы массива могут значительно увеличить время расчета. В этой короткой главе рассматриваются два примера, в которых обычная формула справляется с задачей значительно быстрее формулы массива.
Подсчет дат, когда критерий сформулирован в виде текста. На рис. 12.1 показан набор данных с датами в стандартном формате Excel, то есть в виде порядковых чисел. В тоже время, критерии заданы как число (год) и текст (месяц). Цель – подсчитать, сколько дат соответствуют критерию. Проблема в том, что у нас несоответствие формата данных: в столбце A даты как порядковые номера, а критерий – смесь чисел и текста. На рис. 12.1 приведено пять различных формул, которые можно использовать для достижения цели.
Рис. 12.1. Подсчет количества дат (заданных порядковыми номерами) по двум критериям: году (число) и месяцу (текст)
Давайте подробнее изучим работу этих пяти формул.
- Если вы можете позволить себе вспомогательный столбец, функция СЧЁТЕСЛИ будет самым простым решением.
- Функция МЕСЯЦ возвращает число между 1 и 12, а функция ГОД – число (год).
- Хотя Excel требует, чтобы аргумент функции МЕСЯЦ был представлен датой в числовом формате, этот аргумент может распознать и текст. Однако МЕСЯЦ(Окт) вернет ощибку, а вот если добавить к названию месяца любое число, например, 1, то Excel справится. Используйте, как в формуле выражение Окт1, заданное фрагментом F8&1, или 1Окт, заданное фрагментом 1&F8.
- Формулы с вспомогательными столбцами как правило работают быстрее.
- Если у вас Excel 2007 или более поздний, вы можете использовать функции СЧЁТЕСЛИМН и КОНМЕСЯЦА.
- Вам даны год (в виде числа) и месяц (как текст). Это означает, что вы можете вычислить дату начала и конца месяца, а затем определить даты, попажающие между ними.
- Месяц всегда начинается с первого числа, так что вы можете создать нижнюю границу диапазона конкатенацией: " >=1 " &F8&E8. Операции конкатенации возвращают текст, но это не страшно, т.к. функция СУММЕСЛИМН понимает даты в виде текста.
- Вы используете функцию КОНМЕСЯЦА с аргументом число_месяцев равным нулю; это позволяет получить последнюю дату текущего месяца. Функция КОНМЕСЯЦА является динамической: она возвращает 28 или 29 для февраля и 30 или 31 для любого другого месяца.
- Эта формула является самой быстрой, если вам нужно получить решение в одной ячейке.
- Если у вас Excel версии младше 2007 г., вы можете использовать две функции СЧЁТЕСЛИ, одну – для верхнего диапазона, вторую – для нижнего. Фокус в том, чтобы сначала сосчитать все значения, которые равны или меньше верхней границы, а затем вычесть все значения, которые меньше нижней границы.
- В Excel 2003 или более ранней, чтобы добавить функцию КОНМЕСЯЦА, вам нужно выбрать Инструменты → Надстройки → Анализ Данных.
- Эта формула работает быстрее, чем формулы [4] и [5].
- Функции МЕСЯЦ и ГОД возвращают числа, извлекая их из порядкового номера даты.
- Далее сравниваются два фрагмента, каждый полкченный конкатенацией.
- Функция ТЕКСТ используется для представления чисел в виде текста. Второй аргумент этой функции – формат – определяет, как будет представлено число. Вы может конвертировать весь столбец А в текст, состоящий из 7 символов: 3 буквы месяца и 4 цифры года.
Нахождение объема продаж за год. На рис. 12.4 показан пример несоответствие формата года в критерии Е6 (число) и формата дат в диапазоне А2:А6 (порядковый номер). Цель – найти сумму продаж за год. На рисунке представлены шесть вариантов формул, которые могут решить задачу. Обратите внимание, что в формулах [1] и [2] критерии начала и конца года жестко зашиты в коде, т.к. они не могут изменяться. Это 1/1 и 31/12). Формулы размещены на рисунке в порядка увеличения скорости работы.
Рис. 12.4. Формата года в критерии Е6 (число) не соответствует формату дат в диапазоне А2:А6 (порядковый номер)
= Мир MS Excel/Выборка по дате. - Мир MS Excel
Войти через uID
Войти через uID
Добрый день. Кто поможет с таким вопросом. Имеется таблица из двух столбцов. В одном столбце числа в другом данные. Необходимо сделать выборку в определенном промежутке чисел. Кто может помочь? И можете скинуть ссылку где можно про это почитать. За ранее огромное спасибо. Добрый день. Кто поможет с таким вопросом. Имеется таблица из двух столбцов. В одном столбце числа в другом данные. Необходимо сделать выборку в определенном промежутке чисел. Кто может помочь? И можете скинуть ссылку где можно про это почитать. За ранее огромное спасибо. sazn Решение без сцепления в одну строку (для этого нужен, как я понимаю, макрос).Формула массива (вставляется Ctrl+Shift+Enter)
Формула просматривает в столбце "A" 5000 строк.
Формула массива вставлена так: сначала я выделил F12:F100, затем вставил одну формулу.
Чтобы удалить формулы, нужно снова сначала выделить F12:F100, а затем удалять. Решение без сцепления в одну строку (для этого нужен, как я понимаю, макрос).
Формула массива (вставляется Ctrl+Shift+Enter)
Формула просматривает в столбце "A" 5000 строк.
Формула массива вставлена так: сначала я выделил F12:F100, затем вставил одну формулу.
Чтобы удалить формулы, нужно снова сначала выделить F12:F100, а затем удалять. Karataev
Формула просматривает в столбце "A" 5000 строк.
Формула массива вставлена так: сначала я выделил F12:F100, затем вставил одну формулу.
Чтобы удалить формулы, нужно снова сначала выделить F12:F100, а затем удалять. Автор - Karataev
Дата добавления - 13.04.2016 в 11:33
выберите все данные от 1 строки листа до 179.
Данные - фильтр
Нажмите на стрелочку в поле дат - фильтры по дате - после
Настройте до и после.
Ок.
Похлопаем себе
:hands:
Сцепление данных через notepad++
выберите все данные от 1 строки листа до 179.
Данные - фильтр
Нажмите на стрелочку в поле дат - фильтры по дате - после
Настройте до и после.
Ок.
Похлопаем себе
:hands:
Сцепление данных через notepad++ mathiax90
webmoney: R242692021885
ЯД:410013877261817
Сцепление данных через notepad++ Автор - mathiax90
Дата добавления - 13.04.2016 в 11:40
Инструмент Пакета анализа MS EXCEL «Выборка» извлекает случайную выборку из входного диапазона, рассматривая его как генеральную совокупность. Также случайную выборку можно извлечь с помощью формул.
Пусть имеется некая совокупность данных, которая слишком велика для обработки или построения диаграммы. Для этих целей можно использовать репрезентативную выборку из данной совокупности.
Данную выборку можно получить с помощью инструмента «Выборка» надстройки Пакет анализа ( Analysis ToolPak ).
Надстройка доступна из вкладки Данные , группа Анализ .
СОВЕТ : Подробнее о других инструментах надстройки Пакет анализа и ее подключении – читайте в статье Надстройка Пакет анализа MS EXCEL .
Случайная выборка
Разместим исходную генеральную совокупность в диапазоне A7:A32 (см. файл примера лист Пример ).
Для наглядности все значения совокупности сделаны последовательными числами .
Вызовем надстройку Пакет анализа , выберем инструмент Выборка. Нажмем ОК.
Если диапазон включает и заголовок, то нужно установить галочку в поле Метки . В нашем случае устанавливать галочку не требуется, т.к. заголовок столбца не входит в диапазон A 7: A 32 .
Метод выборки установите Случайный , в поле Число выборок введите 6. Таким образом, из совокупности будет выбрано 6 чисел в случайном порядке (возможны повторы).
В поле Выходной интервал достаточно ввести ссылку на верхнюю ячейку диапазона с результатами (укажем ячейку С7 ). Нажмем ОК.
В результате работы надстройки, MS EXCEL разместил в столбце D 6 значений выбранных случайных образом из диапазона A 7: A 32 .
В принципе, количество случайных значений можно установить даже больше чем значений в исходной совокупности (безусловно, при этом будут повторы).
Аналогичную случайную выборку можно осуществить с помощью формулы =ИНДЕКС($A$7:$A$32;СЛУЧМЕЖДУ(1;СЧЁТ($A$7:$A$32)))
Функция СЛУЧМЕЖДУ() случайным образом выбирает позицию совокупности, из которой нужно взять 1 значение ( для этой функции вероятность выбрать любую строку одинакова ).
Выборка с определенной периодичностью
С помощью инструмента «Выборка» можно осуществить выборку с определенной заданной периодичностью. В этом случае пользователь должен сам убедиться, что данная выборка будет репрезентативной (как минимум, исходная совокупность не должна быть отсортирована ).
Этот же результат можно получить формулой (см. файл примера лист Пример ): =ИНДЕКС($A$7:$A$32;$H$6*(СТРОКА()-СТРОКА($G$6)))
В ячейке H6 содержится период выборки .
Если исходная совокупность имеет цикличность и упорядочена, то можно создать выборку , содержащую значения только из отдельной части цикла. Например, если входной диапазон содержит данные для квартальных продаж за несколько лет, то создание выборки с периодом равным 4 разместит в выходном интервале значения продаж только из заданного квартала.
Гораздо проще для этого использовать формулу (см. файл примера лист Квартал ): =ИНДЕКС($C$9:$C$32;4*(СТРОКА()-СТРОКА($F$8))-4+$F$6) или =СУММЕСЛИМН($C$9:$C$32;$A$9:$A$32;E9;$B$9:$B$32;$F$6&" кв.")
Примечание : Для наглядности строки, относящиеся к нужному кварталу, выделены Условным форматированием .
Выборка из нормального распределения
Пусть имеется генеральная совокупность из 5000 значений случайной величины имеющей стандартное нормальное распределение (см. файл примера лист Выборка из НОРМ ).
Примечание : Случайные значения сгенерированы с помощью формулы =НОРМ.СТ.ОБР(СЛЧИС())
Построим гистограммы распределения для генеральной совокупности и выборки размером 200.
Это удобнее сделать с помощью формулы =ИНДЕКС($A$7:$A$5006; СЛУЧМЕЖДУ(1;СЧЁТ($A$12:$A$5006)))
При каждый раз пересчете листа будет извлекаться новая выборка .
Т.к. значений в выборке значительно меньше, то и ее гистограмма визуально отличается от гистограммы генеральной совокупности .
Читайте также: