Сообщество - MS, Libreoffice & Google docs
Добавить пост
538 постов 13 419 подписчиков

Популярные теги в сообществе:

114

Построение графиков

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

Построение графиков Microsoft Excel, Vba, Плюшка, Длиннопост

Любой, кто решал эту задачку - действовал следующим способом:

1. Создаётся столбец Х;

2. Создаётся столбец Y,  котором происходит расчёт согласно заданной функции;

3. Выделяются два созданных столбца и вставляется график.


Но это просто и скучно. Есть другой способ. Построить график непосредственно из макроса.

Начнём с простого - у нас есть набор точек соответствия X и Y.


Sub Построй_график_по_точкам()

Dim MyChart As Chart

Set MyChart = ActiveSheet.Shapes.AddChart2.Chart

With MyChart

.SeriesCollection.NewSeries

.SeriesCollection(1).Name = "xlXYScatterSmoothNoMarkers"

.SeriesCollection(1).XValues = Array(0#, 0.5, 1#, 1.5, 2#, 2.5, 3#, 3.5, 4#, 4.5, 5#)

.SeriesCollection(1).Values = Array(0#, 0.4794, 0.8415, 0.9975, 0.9093, 0.5985, 0.1411, -0.3508, -0.7568, -0.9775, -0.9589)

.ChartType = xlXYScatterLines ' Соединение точек прямыми

.SetElement msoElementLegendNone

End With

End Sub


Другие варианты отображения линии графика:

.ChartType = xlXYScatterLinesNoMarkers ' Соединение точек прямыми без маркеров

.ChartType = xlXYScatterSmoothNoMarkers ' Сглаженная линия

Более подробно о типах - тут

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


Как не трудно догадаться - вовсе не обязательно иметь готовый набор данных.

Рассмотрим ситуацию, когда требуется построить два графика на одной диаграмме.

Для упрощения восприятия использую две простые функции линий y1 = x - 20, y2 = x + 20.


Sub Создать_диаграмму()

Dim MyChart As Chart

Dim i As Integer, Xmin As Single, dX As Single, Xmax As Single, _

Ymin As Single, Ymax As Single, dY As Single

Dim X() As Single

Dim Y() As Single

Dim Yp() As Single

Xmin = 0: Xmax = 300: dX = 20 ' Сие больше нужно для осей и оформления

Ymin = 0: Ymax = 160: dY = 20

ReDim X(0 To Xmax - Xmin): ReDim Y(0 To Xmax - Xmin, 1 To 2)

ReDim Yp(0 To Xmax - Xmin)

For i = 0 To Xmax - Xmin Step 1

X(i) = Xmin + i

' Заполнение данных первого графика

Y(i, 1) = X(i) - 20

' Заполнение данных второго графика

Y(i, 2) = X(i) + 20

Next i

' создадим новую диаграмму и зададим ей габаириты

Set MyChart = ActiveSheet.Shapes.AddChart2(, , , , 300, 200).Chart

For i = 1 To 2

For j = 0 To Xmax - Xmin Step 1

Yp(j) = Y(j, i)

Next j

With MyChart

.SeriesCollection.NewSeries

.SeriesCollection(i).XValues = X

.SeriesCollection(i).Values = Yp

.ChartType = xlXYScatterSmoothNoMarkers

End With

Next i

End Sub


При задании новой диаграммы можно задать в том числе и положение диаграммы на листе

AddChart2(Стиль,XlChartType,слева,сверху,ширина,высота,NewLayout)

В итоге получим вот такую диаграмму:

Построение графиков Microsoft Excel, Vba, Плюшка, Длиннопост

В дальнейшем можно обработать её  как обычную - задать цвета, толщины и т.д. Но можно это сразу поручить нашему макросу:


Sub Создать_диаграмму()

Dim MyChart As Chart

Dim i As Integer, Xmin As Single, dX As Single, Xmax As Single, _

Ymin As Single, Ymax As Single, dY As Single

Dim X() As Single

Dim Y() As Single

Dim Yp() As Single

Xmin = 0: Xmax = 300: dX = 20 ' Сие больше нужно для осей и оформления

Ymin = 0: Ymax = 340: dY = 20

ReDim X(0 To Xmax - Xmin): ReDim Y(0 To Xmax - Xmin, 1 To 2)

ReDim Yp(0 To Xmax - Xmin)

For i = 0 To Xmax - Xmin Step 1

X(i) = Xmin + i

Y(i, 1) = X(i) - 20

Y(i, 2) = X(i) + 20

Next i

Set MyChart = ActiveSheet.Shapes.AddChart2(, , 0, 0, 400, 230).Chart

For i = 1 To 2

For j = 0 To Xmax - Xmin Step 1

Yp(j) = Y(j, i)

Next j

With MyChart

.SeriesCollection.NewSeries

.SeriesCollection(i).XValues = X

.SeriesCollection(i).Values = Yp

.ChartType = xlXYScatterSmoothNoMarkers

End With

Next i

With MyChart

.SetElement (msoElementPrimaryCategoryGridLinesMajor)

' Включаю отображение названия осей

.Axes(xlCategory, xlPrimary).HasTitle = True

.Axes(xlValue, xlPrimary).HasTitle = True

.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Расход Go т/ч"

.Axes(xlValue, xlPrimary).AxisTitle.Text = "Давление кгс/кв.см."

' Выключаю отображение легенды

.SetElement (msoElementLegendNone)

' Выключаю отображения заголовка диаграммы

.SetElement (msoElementChartTitleNone)

' Выставляем параметры осей

.Axes(xlCategory).MinimumScale = Xmin

.Axes(xlCategory).MaximumScale = Xmax

.Axes(xlCategory).MajorUnit = dX

.Axes(xlValue).MinimumScale = Ymin

.Axes(xlValue).MaximumScale = Ymax

.Axes(xlValue).MajorUnit = dY

End With

' Оформление гризонтальной оси

MyChart.Axes(xlCategory).Select

With Selection.Format.Line

.Visible = msoTrue

.ForeColor.RGB = RGB(0, 0, 0)

.ForeColor.TintAndShade = 0

.ForeColor.Brightness = 0

.Transparency = 0

.Visible = msoTrue

.Weight = 1.25

End With

' Оформление вертикальной оси

MyChart.ChartArea.Select

MyChart.Axes(xlValue).Select

With Selection.Format.Line

.Visible = msoTrue

.ForeColor.RGB = RGB(0, 0, 0)

.ForeColor.TintAndShade = 0

.ForeColor.Brightness = 0

.Transparency = 0

.Visible = msoTrue

.Weight = 1.25

End With

' Оформление горизонтальной сетки

MyChart.Axes(xlValue).MajorGridlines.Select

With Selection.Format.Line

.Visible = msoTrue

.DashStyle = msoLineDash

.Visible = msoTrue

.ForeColor.RGB = RGB(0, 176, 240)

.Transparency = 0

End With

' Оформление вертикальной сетки

MyChart.Axes(xlCategory).MajorGridlines.Select

With Selection.Format.Line

.Visible = msoTrue

.DashStyle = msoLineDash

.Visible = msoTrue

.ForeColor.RGB = RGB(0, 176, 240)

.Transparency = 0

End With

End Sub


По итогу диаграмма будет выглядеть так:

Построение графиков Microsoft Excel, Vba, Плюшка, Длиннопост

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

Кому то это покажется слишком сложным, однако открою маленький секрет - очень редкие люди пишут макрос с нуля. В 90% достаточно иметь готовый макрос (см листинг выше), заменить в нём пару строк (сменить функции, изменить диапазоны...) и всё. По итогу построение занимает меньше времени чем построение классическим способом.

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

Ну и всегда есть вариант удивить преподавателя (0_о).

Показать полностью 2
54

Какая дата будет через месяц

Рассчитать, какая дата наступит через месяц (или несколько месяцев) можно функциями.


1 способ. Функция ДАТАМЕС (EDATA)

Аргументы этой функции такие:


ДАТАМЕС (Дата; Кол-во месяцев), где

Дата — это начальная дата,

Кол-во месяцев — это число месяцев, которые нужно добавить к дате или вычесть. Для добавления указывается положительное число, для вычитания — отрицательное.


ВАЖНО!!! При фактическом отсутствии дня в получившемся месяце будет получена предыдущая реально существующая дата.


Пример: Рассчитать дату отгрузки через месяц от даты заказа:

Какая дата будет через месяц Microsoft Excel, Дата, Формула, Таблицы Excel, Функция, Длиннопост

2 способ. Функция ДАТА (DATA)

Аргументы этой функции:


ДАТА(Год; Месяц; День), где

Год — год для даты, который может быть выражен числом или функцией ГОД от указанной даты,

Месяц — месяц для даты, который может быть выражен числом или функцией МЕСЯЦ от указанной даты плюс (минус) нужное количество месяцев,

ДЕНЬ — день для даты — число или функция ДЕНЬ от указанной даты.


ВАЖНО!!! При фактическом отсутствии дня в получившемся месяце будет получена следующая реально существующая дата.


Пример: Рассчитать дату отгрузки через месяц от даты заказа:

Какая дата будет через месяц Microsoft Excel, Дата, Формула, Таблицы Excel, Функция, Длиннопост

Получается, если нужной даты при расчете нет, функция ДАТАМЕС даст нам предыдущую дату, а функция ДАТА — следующую. Обратите внимание на это существенное отличие!


Бонус. Если дата нужна не просто существующая, но еще и рабочая!

Добавьте к нужной формуле функцию РАБДЕНЬ (WORKDAY) с одновременным добавлением и вычитанием одного дня:

РАБДЕНЬ(дата -1;1) — чтобы сработать в +день, или

РАБДЕНЬ(дата +1;-1) — чтобы сработать в -день.

Пример всех функций в одной таблице:

Какая дата будет через месяц Microsoft Excel, Дата, Формула, Таблицы Excel, Функция, Длиннопост
Показать полностью 3
22

Гистограмма частот

Обзор 3х способов: вручную, Пакетом анализа, стандартной диаграммой (с версии 2016).

***


В статистическом анализе часто требуется построить график, отображающий частоту элементов в выборке. Также такой график позволяет узнать, пригодны ли измерения для последующего анализа, и если пригодны, то какой именно анализ позже следует использовать. Наиболее распространен анализ данных при нормальном распределении в выборке. Нормальное распределение (очень упрощенно) — это когда большинство значений в ряду приближены к среднему значению, а остальные значения встречаются тем реже, чем ближе они к минимальному или максимальному значению ряда. Графически это можно представить так:

Гистограмма частот Microsoft Excel, Диаграмма, Нормальное распределение, Частота, Статистика, Видео, Без звука, Длиннопост

***Для тех, кто знаком со статистическими исследованиями, сразу прошу прощения за элементарность в объяснении и картинках. Мне бы не хотелось здесь «грузить» читателя, незнакомого со статистикой, расчетами стандартных отклонений, правилами 6-ти сигм и т.д., поэтому стараюсь здесь описывать ситуацию максимально простыми терминами.


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

Это была «матчасть» вкратце :)


Практический пример

Теперь перейдем к задаче в Excel.

Допустим, есть ряд данных с измерениями (всего 56 измерений). Допустим, это измерения длины детали, которую вытачивает станок. Эталонная длина детали — 50 мм. Но в реальности длина деталей отличается от эталона:

Гистограмма частот Microsoft Excel, Диаграмма, Нормальное распределение, Частота, Статистика, Видео, Без звука, Длиннопост

Требуется построить график частот для просмотра и анализа отклонений.


Решение. Часть 1. Строим интервалы частот

Для начала следует определить, сколько интервалов частот имеет смысл сделать. На самом деле, их может быть любое количество, желательно, не менее 6, но и не слишком много. Для 56 измерений я возьму 9 интервалов. Размер каждого интервала рассчитаем по формуле

=(МАКС.знач.-МИН.знач)/КОЛИЧЕСТВО интервалов:

Гистограмма частот Microsoft Excel, Диаграмма, Нормальное распределение, Частота, Статистика, Видео, Без звука, Длиннопост

Далее строим список интервалов от минимального до максимального значения через этот найденный промежуток. Первая точка — минимальное значение ряда, каждая следующая — через найденный промежуток, последняя точка — максимальная точка ряда+небольшой запас, иначе максимальная точка не будет учтена при расчете частот. Итого получается 10 точек (тут маленькое видео, чтоб было понятно):

По поводу последней точки: ее нужно увеличить небольшим запасом, хотя бы на 0,01, чтобы максимальная точка тоже учитывалась при дальнейшем расчете частот:

Гистограмма частот Microsoft Excel, Диаграмма, Нормальное распределение, Частота, Статистика, Видео, Без звука, Длиннопост

После определения интервалов можно поступить 2 способами: самостоятельно рассчитать частоты и построить график, или воспользоваться пакетом анализа и с его помощью построить график. Начну со способа «все сделать самостоятельно».


Часть 2. Способ 1, самостоятельный

Используя функцию ЧАСТОТА, распределим значения по интервалам. Гифка с действиями:

2. Строим гистограмму получившихся частот. Я воспользовалась кнопкой Быстрый анализ:

Гистограмма частот Microsoft Excel, Диаграмма, Нормальное распределение, Частота, Статистика, Видео, Без звука, Длиннопост

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

Гистограмма частот Microsoft Excel, Диаграмма, Нормальное распределение, Частота, Статистика, Видео, Без звука, Длиннопост

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


Часть 2. Способ 2, Пакет анализа

Имея список интервалов, построить гистограмму распределения частот можно с помощью Пакета анализа. Пакет анализа — это надстройка, входящая в Excel, но по умолчанию не включенная.

Чтобы активировать надстройку, надо перейти в Параметры Excel, выбрать Надстройки - Перейти и установить флаг Пакет анализа. Команда Анализ данных будет добавлена на вкладку Данные:

Теперь, чтобы построить диаграмму, выполняем:

Анализ данных — Гистограмма — ОК.

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

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


Часть 3. Способ последний, самый новый

Счастливым обладателям Excel 2016 и выше даже интервалы считать не надо: в этих версиях диаграмма частот появилась как стандартная, достаточно только иметь ряд данных для анализа:

Гистограмма частот Microsoft Excel, Диаграмма, Нормальное распределение, Частота, Статистика, Видео, Без звука, Длиннопост

Распределение по интервалам при этом произойдет автоматически, но если необходимо, количество интервалов можно изменить. Подписи горизонтальной оси содержат сразу охват интервала, например, [49,36 49,72], что значит, интервал 49,36-49,72. Для изменения количества интервалов нужно открыть настройки горизонтальной оси и задать там для интервалов либо длину, либо их количество. Я выставила 9, как и в других случаях:

Гистограмма частот Microsoft Excel, Диаграмма, Нормальное распределение, Частота, Статистика, Видео, Без звука, Длиннопост

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

Показать полностью 8 4
16

Расчет сдельной оплаты

Добрый день Пекобушнеки.

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

А дело у нас такое: надо бы разделить заработанные деньги между членами бригады, чтобы по справедливости. Делить будем, конечно же, в в нашем родном американском Экселе.


Итак: в конце месяца бригада заработала сколько-то денег в сумме.


Каждый член бригады работает по разному, процент участия обговаривается и суммарно составляет 100%, которые делятся: Первый 35%, Второй 33%, Третий 15%, Четвертый 17%.


Но работники люди живые и иногда болеют, поэтому кол-во выходов на работу тоже разное: Первый 13, Второй 21, Третий 22, Четвертый 20.


Как бы учесть процент участия и кол-во отработанных дней, да раздать зарплату честно и без обижулек?

95

Excel. Долгая дорога оцифровки. Часть 9.  Оформление графиков, или отображение поиска решения

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

Итак, по сути потребуется решить два вопроса:

1. Построить ход поиска с помощью стрелки/стрелок.

2. Совместить построенный график с изначальным рисунком.

Т.е. получить что то похожее на вот это:

Excel. Долгая дорога оцифровки. Часть 9.  Оформление графиков, или отображение поиска решения Microsoft Excel, Vba, Плюшка, Длиннопост

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

Часть 1. Построение поиска решения.

Итак, у нас есть заданные аргументы (G2, t1в) и результат расчёта Р2. На графике сие будет выглядеть как одна точка с координатами X = G2 = 200 (в нашем примере) и Y = Р2 = 0,065

Существуют минимум три метода построения стрелки поиска:

Вариант 1. Для вертикальной и горизонтальной части строим независимые линии.

Excel. Долгая дорога оцифровки. Часть 9.  Оформление графиков, или отображение поиска решения Microsoft Excel, Vba, Плюшка, Длиннопост

После построения настраиваем цвета, указываем наличие стрелки, и т.д.

Для вертикальной линии второй точкой указывается точка с равным значением по Х и минимумом по бумажному графику Y.

Для горизонтальной линии второй точкой указывается точка с равным значением по Y и минимумом по бумажному графику X.

Минимумы и максимумы диаграммы выставляются равными минимумам и максимумам бумажного рисунка.

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


Вариант 2. Единая линия поиска.

Excel. Долгая дорога оцифровки. Часть 9.  Оформление графиков, или отображение поиска решения Microsoft Excel, Vba, Плюшка, Длиннопост

Выставление значений дополнительных точек, и значений осей аналогично Варианту 1.


Вариант 3. Использование погрешностей для указания поиска решения.

Excel. Долгая дорога оцифровки. Часть 9.  Оформление графиков, или отображение поиска решения Microsoft Excel, Vba, Плюшка, Длиннопост

Если точка одна, то для отображения линий погрешности необходимо перейти в настройки предела погрешности по Х и по Y поочерёдно и...

Excel. Долгая дорога оцифровки. Часть 9.  Оформление графиков, или отображение поиска решения Microsoft Excel, Vba, Плюшка, Длиннопост

Отметить

- минус

- без точки

- величина погрешности "пользовательская".

В качестве отрицательной величины погрешности указываем соответственно значение X и Y

Excel. Долгая дорога оцифровки. Часть 9.  Оформление графиков, или отображение поиска решения Microsoft Excel, Vba, Плюшка, Длиннопост

Если есть желание получить стрелку направленную к оси Y, а ось Х начинается не с 0 (в нашем случае с 2-ти), то потребуется сделать ячейку рассчитывающую смещение относительно 0.

В нашем примере сделаем такое и для X и Y:

ось Х сдвинута на 20. Соответственно имеем ячейку Хзаданное  -  Хсмещения = 200 - 20

ось Y сдвинута на 0,02 Соответственно имеем ячейку Yзаданное - Yсмещения

Это значения не статичны, т.е. они пересчитаются при изменении исходных данных.

При указании отображения погрешностей ссылаемся на данные ячейки.

Excel. Долгая дорога оцифровки. Часть 9.  Оформление графиков, или отображение поиска решения Microsoft Excel, Vba, Плюшка, Длиннопост

Аналогично первым вариантам указываются свойства линий.

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

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

Excel. Долгая дорога оцифровки. Часть 9.  Оформление графиков, или отображение поиска решения Microsoft Excel, Vba, Плюшка, Длиннопост

Однако можно сделать выноску для той самой, единственной точки.

Результаты всех трёх способов не сильно отличаются:

Excel. Долгая дорога оцифровки. Часть 9.  Оформление графиков, или отображение поиска решения Microsoft Excel, Vba, Плюшка, Длиннопост

Часть 2. Совмещение построенного графика с изначальным рисунком.

И опять есть минимум три варианта.

Вариант 1. Использование рисунка в качестве подложки под областью построения (то, что расположено внутри границ осей). Для этого рисунок сначала подготавливается (обрезается по размерам построения, при этом подписи осей оказываются обрезанными), а затем вставляется по пути: Формат области построения – Заливка – Рисунки и текстура – Файл / из буфера обмена;


Вариант 2. Использование рисунка в качестве подложки области диаграммы (вкладка Формат области диаграммы – Заливка – Рисунки и текстура - Файл) вставляется рисунок графика (предварительно подготовленный и очищенный. Необходимо также учитывать, что потребуется некоторая ширина полей для выставления подписей). Совмещаются границы графика Excel с границами графика рисунка перетягиванием за маркеры границы графика (перемещение указал стрелками).

Excel. Долгая дорога оцифровки. Часть 9.  Оформление графиков, или отображение поиска решения Microsoft Excel, Vba, Плюшка, Длиннопост

выставляются границы осей графика Excel в соответствии с границами графика (если не выставили ранее). При необходимости производится отключение отображения подписей осей, сетка и название диаграммы.

Excel. Долгая дорога оцифровки. Часть 9.  Оформление графиков, или отображение поиска решения Microsoft Excel, Vba, Плюшка, Длиннопост

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

Excel. Долгая дорога оцифровки. Часть 9.  Оформление графиков, или отображение поиска решения Microsoft Excel, Vba, Плюшка, Длиннопост

при необходимости можно построить дополнительную линию. В качестве примера построена дополнительная кривая при 40°С при помощи созданной пользовательской функции при заданной температуре 40°С и переменной влажности. Аналогично построена дополнительная линия на первом рисунке

Excel. Долгая дорога оцифровки. Часть 9.  Оформление графиков, или отображение поиска решения Microsoft Excel, Vba, Плюшка, Длиннопост

Вариант 3. При третьем варианте рисунок вставляется на лист Excel, построенный график/ подготовленная диаграмма размещается над рисунком, при этом заливка поля построения и самой диаграммы "отсутствует" или "прозрачная". После совмещения изображение и диаграмма фиксируются между собой как это было указано в посте "Нестандартные заголовки диаграмм".

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

Excel. Долгая дорога оцифровки. Часть 9.  Оформление графиков, или отображение поиска решения Microsoft Excel, Vba, Плюшка, Длиннопост

Отдельно стоят диаграммы состоящие из расположенных рядом двух и более диаграмм.

Их оформление, опять же, может быть реализовано тремя способами.

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

Способ 2 - применение первого варианта, наложение графика на область построения (описано выше). Т.е. строим два независимых графика для левой и правой части, накладываем области построения и размещаем взаимно друг другу до совпадения минимума и максимума.

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

Рассмотрим один из вариантов построения стрелки на диаграмме, состоящей из двух диаграмм, при этом ширина клеток и величина шага для правого и левого графика разная.

Excel. Долгая дорога оцифровки. Часть 9.  Оформление графиков, или отображение поиска решения Microsoft Excel, Vba, Плюшка, Длиннопост

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

Шаг 1. Построение левого графика (синий график, синяя ось, синие данные).

1. Построить точечный график по исходным данным, причём заложить небольшой перехлёст по Х (установлено 80 вместо 70-ти по рисунку);

2. Сделать подложку под диаграмму (используется весь рисунок, без обрезок или разделения на две части);

3. Растянуть область построения на рисунок;

4. Задать значения оси (диапазон) Y в соответствии с оцифровкой;

5. Задать значения оси (диапазон) Х таким образом, чтобы Хмин было равно минимальному значению на рисунке (30), а Хмакс подобрать таким образом, чтобы совпали значения рисок (40=40, 50=50, 60=60, 70=70).

Шаг 2. Построение правого графика (красный график, красный ось, красный данные).

1. Построить точечный график по исходным данным, причём минимум Х заложить равным минимуму по второй оси (0);

2. Указать построение по вспомогательным осям;

3. Задать значения вспомогательной оси (диапазон) Y в соответствии с оцифровкой;

4. Задать значения оси (диапазон) Х таким образом, чтобы Хмакс было равно максимальному значению по второй оси рисунка, а Хмин подбрать таким образом, чтобы начало второго графика легло на минимум второй оси Х рисунка.

Шаг 3. Убрать отображение подписей осей, сетки и т.д. Настроить цвета линий.


============================

Для кого то это покажется элементарным, но я на своей практике не один раз ломал голову как выполнить графическое оформление поиска решения. Базовыми знаниями поделился. Всё дальнейшее зависит от вас. Будут вопросы - помогу по мере сил.


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

1. Теория

2. Снятие данных с рисунка

3. Апроксимация простых графиков

4. Макрос по созданию макросов простых функций

5. Создание макроса функции двух аргументов

6. Кусочная интерполяция

7. Макрос по созданию макросов на основе кусочной интерполяции

8. Обратная функция или поиск корней

9. Отображение поиска решения (данный пост).

Показать полностью 14
242

Поиск наименований по ключевому слову в Excel

Задача: формировать список значений, найденных по ключевому слову. Сразу продемонстрирую результат, который получится в итоге:

Начало работы

Что имеем: 1) список данных (В4:В30), предварительно очищенный от дубликатов, 2) ячейка для ввода ключевого слова (Е1) и 3) диапазон под вывод результатов (предварительно пронумерованный по всей длине списка):

Поиск наименований по ключевому слову в Excel Microsoft Excel, Таблицы Excel, Урок, Впр, Видео, Без звука, Длиннопост

1 шаг. Формирование нумерованного списка по поиску


В дополнительный столбец (например, А) вносим формулу:

=ЕСЛИ (ПОИСК($E$1;B4)>0; СЧЁТЕСЛИ($B$4:B4;"*"&$E$1&"*");0)


где

ПОИСК($E$1;B4) — выполняет проверку совпадений. Если совпадение есть, выводит порядковый номер первого совпадающего символа. На самом деле неважно, какое это будет числовое значение, главное, что оно числовое. Если совпадения нет, выходит #ЗНАЧ. Поэтому

ЕСЛИ (ПОИСК($E$1;B4)>0 следует понимать как «если совпадение найдено».


Часть СЧЁТЕСЛИ($B$4:B4;"*"&$E$1&"*") означает - при нахождении неточного совпадения («звездочки» (*) указывают, что совпадение неточное) выводи значение повтора - это будет 1,2,3...


В целом, это выглядит так:

Поиск наименований по ключевому слову в Excel Microsoft Excel, Таблицы Excel, Урок, Впр, Видео, Без звука, Длиннопост

На заметку! Функция ПОИСК выполняет поиск без учета регистра. Если нужен поиск с учетом регистра, вместо ПОИСК надо использовать функцию НАЙТИ.


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

С помощью ВПР в столбце для результатов (Е) находим результаты сравнения номеров из столбца D с номерами из А. Через ЕСЛИОШИБКА скрываем Н/Д (''''):

Поиск наименований по ключевому слову в Excel Microsoft Excel, Таблицы Excel, Урок, Впр, Видео, Без звука, Длиннопост

3* шаг. Наводим красоту

Шаг, возможно, не обязательной, так как основная работа сделала, поэтому помечен *. Но лично я люблю, когда все красиво и понятно, поэтому про «красоту» тоже расскажу.


Во-первых, сейчас уже можно скрыть вспомогательный столбец А, он для показа не нужен.

Во-вторых, лучше скрыть ненужные порядковые номера из столбца D. Пусть здесь работает такой принцип: сколько результатов найдено, столько номеров и видно.


Исходно номеров проставлено столько, сколько всего позиций в списке (ну а вдруг?). Но чаще всего они в таком количестве не нужны, поэтому схитрим и скроем их через Условное форматирование:

1. Выделяем ячейки D4:D30, выбираем Главная — Условное форматирование — Создать правило, Использовать формулу…


Устанавливаем формулу: =$Е4='''', что значит: если ячейка столбца Е пустая.


Нажимаем кнопку Формат, выбираем вкладку Шрифт, цвет Белый.

Жмем ОК во всех окнах, наслаждаемся результатом :)

Поиск наименований по ключевому слову в Excel Microsoft Excel, Таблицы Excel, Урок, Впр, Видео, Без звука, Длиннопост

Если бы можно было приложить файл, тоже бы добавила. Если кто знает способ, подскажите.

Показать полностью 4
80

Excel. Долгая дорога оцифровки. Часть 7. Автоматическое создание макроса функции с использованием кусочной интерполяции

По аналогии с Excel. Долгая дорога оцифровки. Часть 4. Макрос по созданию макросов апроксимации простых графиков полиномом  и Excel. Долгая дорога оцифровки. Часть 6. Кусочная интерполяция не сложно выполняется макрос по созданию макросов оцифровки простых графиков с использованием кусочной интерполяции.

Excel. Долгая дорога оцифровки. Часть 7. Автоматическое создание макроса функции с использованием кусочной интерполяции Microsoft Excel, Vba, Плюшка, Длиннопост

Описание вводимых данных аналогично ранее изложенному.

Если ещё немного развить тему, то и макрос создания макросов функции с двумя аргументами не проблема:

Excel. Долгая дорога оцифровки. Часть 7. Автоматическое создание макроса функции с использованием кусочной интерполяции Microsoft Excel, Vba, Плюшка, Длиннопост

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


Основное нововведение - определение количества графиков. Если вспомните ещё в Excel. Долгая дорога оцифровки. Часть 2. Забираем данные с листа я писал, что что "снятие точек производить от меньшего Х к большему. При наличии диаграммы зависимости от двух аргументов типаY(X1, X2) начиная с графика меньшего Х2. С обязательным условием - каждая следующая линия должна начинаться с Х меньшего, чем закончилась предыдущая.". И теперь можно этим воспользоваться - определить количество переходов на новую линию по уменьшению Х по сравнению с предыдущим.


For i = 2 To xVal.Count

If i = xVal.Count Then

Nkon(Ndiap) = i

End If

If xVal.Rows(i) < xVal.Rows(i - 1) Then

Nkon(Ndiap) = i - 1

Ndiap = Ndiap + 1

Nna4(Ndiap) = i

End If

Next i


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


Результирующий макрос будет иметь вид:


' Поправки Сербия Панчево Страница 34 из 77 Нижний рисунок

Public Function ТЭХ_ПТ80_Рис3(ByRef Go As Single, ByRef CkH As Single) As Single

Dim krit_kriv As Variant

krit_kriv = array(2.96,3.06,3.15)

Dim kriv As Variant

kriv = Array(-0.00271242 * Go + 0.100817, _

-0.00252906 * Go + 0.230858, _

0.000276671 * Go ^ 2 -0.203078 * Go + 31.5862)

ТЭХ_ПТ80_Рис3= kus_interp(krit_kriv, kriv, CkH, 2)

End Function


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


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


Если возникнут вопросы как сие работает, распишу. Если у кого то что то не заработает - обращайтесь, посмотрю.


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

Excel. Долгая дорога оцифровки. Часть 7. Автоматическое создание макроса функции с использованием кусочной интерполяции Microsoft Excel, Vba, Плюшка, Длиннопост

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


Например диаграмма с несколькими независимыми графиками типа такой. Можно либо сделать 3 независимых макроса, либо один с выбором графика.

Excel. Долгая дорога оцифровки. Часть 7. Автоматическое создание макроса функции с использованием кусочной интерполяции Microsoft Excel, Vba, Плюшка, Длиннопост

С помощью автоматического создания макросов

Excel. Долгая дорога оцифровки. Часть 7. Автоматическое создание макроса функции с использованием кусочной интерполяции Microsoft Excel, Vba, Плюшка, Длиннопост

Позволит получить (текстовка от графика отличного от представленного выше рисунка)


' ТЭХ ПТ80 Рис.3 Давление в отборах при конденсационном режиме [МПа]

Public Function ТЭХ_ПТ80_Рис3(ByRef Go As Single, ByRef Название_графика As Variant) As Variant

Dim krit_graph As Variant

krit_graph = array(1,2,3)

Select Case Название_графика

Case krit_graph(0)

ТЭХ_ПТ80_Рис3 = -0.0027124 * Go ^ 1 + 0.10082

Case krit_graph(1)

ТЭХ_ПТ80_Рис3 = -0.0025397 * Go ^ 1 + 0.23509

Case krit_graph(2)

ТЭХ_ПТ80_Рис3 = -0.0026659 * Go ^ 1 + 0.4529

Case Else

ТЭХ_ПТ80_Рис3 = 999999999999999

End Select

End Function


При желании указывать название графика правится krit_graph = array("Go","Qo","qt").


Ну и гораздо более сложноподчинённые, например что реализовано у меня:

Создание макроса для варианта когда критерий зависит от своего критерия
Excel. Долгая дорога оцифровки. Часть 7. Автоматическое создание макроса функции с использованием кусочной интерполяции Microsoft Excel, Vba, Плюшка, Длиннопост

Диаграммы режимов ПТ типа ПТ-80

Excel. Долгая дорога оцифровки. Часть 7. Автоматическое создание макроса функции с использованием кусочной интерполяции Microsoft Excel, Vba, Плюшка, Длиннопост

Диаграммы режимов типа Т-250

Excel. Долгая дорога оцифровки. Часть 7. Автоматическое создание макроса функции с использованием кусочной интерполяции Microsoft Excel, Vba, Плюшка, Длиннопост

Нормативной температуры сетевого подогревателя.

Excel. Долгая дорога оцифровки. Часть 7. Автоматическое создание макроса функции с использованием кусочной интерполяции Microsoft Excel, Vba, Плюшка, Длиннопост

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


Из того на что стоит обратить внимание, или маленькие лайфхаки:

1. Не всегда есть разметка осей. Например на диаграмме на последнем скрине вертикальная ось не размечена. Но она в данном случае не нужно. Важно иметь одинаковое значение для левого и правого графиков. Как правило я принимаю в качестве минимального значения оси - 0, в качестве максимального - число клеток (например 12-ть).

2. Внимание! Ось Х не обязательно горизонтальная при "снятии точек"! Например на диаграмме на последнем скрине  для правого графика удобно взять в качестве оси Х вертикальную ось а в качестве Y - горизонтальную. Тогда результат обработки левой номограммы будет сразу выступать в качестве аргумента для правой номограммы.

3. Есть варианты оцифровки, когда лучше привязываться не к значениям осей, а к клеточкам :) Да, звучит дико, но иногда проще внести пересчёт внутри макроса, чем реализовать оцифровку по данным осей.  Например диаграмма ниже - обратите внимание, что вертикальная ось не обозначена, зато горизонтальная в левой диаграмме разбита на 3 участка с разным масштабом.

Excel. Долгая дорога оцифровки. Часть 7. Автоматическое создание макроса функции с использованием кусочной интерполяции Microsoft Excel, Vba, Плюшка, Длиннопост

Упд. Вспомнил ещё про важную часть - обратные функции. Т.е. есть макрос (готовый!), который по известным Х1, Х2... находит Y. Иногда требуется с использованием данного макроса и известных Y и X1 найти X2... Но об этом в следующий раз. А то и так пост разросся.

Показать полностью 9
10

Рабы, Наркотики и Лада Невада

Всем привет! Встала задача составить смету по товару, который попал в заказ. На данный момент подошла бы формула "сцепить если" товар был заказан, но такой формулы нет. приходиться всё делать вручную. Подскажите пожалуйста альтернативы. Как можно избавиться от формул-посредников?

Рабы, Наркотики и Лада Невада Microsoft Excel, Формула, Помощь, Подсчет
Рабы, Наркотики и Лада Невада Microsoft Excel, Формула, Помощь, Подсчет
Рабы, Наркотики и Лада Невада Microsoft Excel, Формула, Помощь, Подсчет

Ссылка на файл: https://docs.google.com/spreadsheets/d/19abHPsgNQ92-8EW0-RTY...

Показать полностью 2
43

Excel. Долгая дорога оцифровки. Часть 6. Кусочная интерполяция

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

Excel. Долгая дорога оцифровки. Часть 6. Кусочная интерполяция Microsoft Excel, Vba, Плюшка, Длиннопост

Выходом из данной ситуации является разбиение данных на несколько частей, в данном случае 2 с общей точкой Do=428, создание кусучнозаданной функции (ЕСЛИ меньше 428 одна функция, если больше - вторая функция). Но так в данном случае, а если надо сделать два, три...и больше разбиений? Кропотливая работа. Но зачем, если можно заставить Excel в автоматическом режиме выбирать малое количество точек и проводить через них интерполяционную функцию.

Отчасти кусочную интерполяцию показывал в прошлом посте серии ( Excel. Долгая дорога оцифровки. Часть 5. Создание пользовательской функции для двух аргументов. Ручной вариант ) при поиске решения между заданных критериев.


Как простые варианты рассмотрим кусочную интерполяцию по двум и по четырём точкам для заданных ниже данных.

Excel. Долгая дорога оцифровки. Часть 6. Кусочная интерполяция Microsoft Excel, Vba, Плюшка, Длиннопост

Допустим нужно определить значение Y при X = 2.5.

При кусочной интерполяции по двум точкам используются две ближайшие заданные точки к X = 2.5. , т.е. 2 и 3, через данные точки провидится линия,и по ней находится Y при X = 2.5..

Excel. Долгая дорога оцифровки. Часть 6. Кусочная интерполяция Microsoft Excel, Vba, Плюшка, Длиннопост

При кусочной интерполяции по четырём точкам используются две ближайшие заданные точки к X = 2.5. справа и две две ближайшие заданные точки к X = 2.5. слева, т.е. 1 и 2 и 3 и 4, через данные точки провидится кривая (полином 3-й степени),и по ней находится Y при X = 2.5.

Это справедливо для данных за 2-й и перед предпоследней известной точкой. Для данных отрезков и для экстраполяции использую линейную интерполяцию (по 2-м точкам).

Excel. Долгая дорога оцифровки. Часть 6. Кусочная интерполяция Microsoft Excel, Vba, Плюшка, Длиннопост

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

Вообще правило такое, в зависимости от вида графика:

Excel. Долгая дорога оцифровки. Часть 6. Кусочная интерполяция Microsoft Excel, Vba, Плюшка, Длиннопост

Ну и собственно с помощью чего сие выполняется:


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

======

' Интерполяция по 2-м, 3-м или 4-м ближайшим (до и после) к заданной (Xisk) точке

' В подпрограмму передаются все заданные точки

' Интерполяция происходит косочно, по количеству заданных точек с учётом расположения заданного Х

' Данные из листа Excel.

Public Function kus_interp_Ex(Xt As Range, Yt As Range, Xisk As Single, Optional ByVal toch As Integer = 2) As Variant

Dim i As Long

Dim xd() As Double

Dim yd() As Double

Dim cd() As Double

' toch - указание поиска решения с использованием количества точек (2, 3, 4).

Select Case toch

Case 2 ' Уравнение а·х+b

kus_interp_Ex = linterp(Xt.Rows(Xt.Count - 1), Xt.Rows(Xt.Count), Yt.Rows(Xt.Count - 1), Yt.Rows(Xt.Count), Xisk)

For i = 1 To Xt.Count - 1

If Xisk < Xt.Rows(i + 1) Then

kus_interp_Ex = linterp(Xt.Rows(i), Xt.Rows(i + 1), Yt.Rows(i), Yt.Rows(i + 1), Xisk)

Exit For

End If

Next i

Case 3 ' Уравнение а·х^2+b·x+c Интерполяция по принципу х1 Х х2 х3

kus_interp_Ex = kubterp(Xt.Rows(Xt.Count - 2), Xt.Rows(Xt.Count - 1), Xt.Rows(Xt.Count), _

Yt.Rows(Xt.Count - 2), Yt.Rows(Xt.Count - 1), Yt.Rows(Xt.Count), Xisk)

For i = 1 To Xt.Count - 2

If Xisk < Xt.Rows(i + 1) Then

kus_interp_Ex = kubterp(Xt.Rows(i), Xt.Rows(i + 1), Xt.Rows(i + 2), _

Yt.Rows(i), Yt.Rows(i + 1), Yt.Rows(i + 2), Xisk)

Exit For

End If

Next i

Case 4 ' Уравнение а·х^3+b·x^2+c·x+d Интерполяция по принципу х1 х2 X х3 x4

ReDim xd(1 To 4) As Double

ReDim yd(1 To 4) As Double

If Xisk < Xt.Rows(2) Then ' Экстраполяция ДО и интерполяция ДО второй известной точки - линейна

kus_interp_Ex = linterp(Xt.Rows(1), Xt.Rows(2), Yt.Rows(1), Yt.Rows(2), Xisk)

Else

If Xisk >= Xt.Rows(Xt.Count - 1) Then ' Экстраполяция ЗА и интерполяция ПОСЛЕ второй известной точки - линейна

kus_interp_Ex = linterp(Xt.Rows(Xt.Count - 1), Xt.Rows(Xt.Count), Yt.Rows(Xt.Count - 1), Yt.Rows(Xt.Count), Xisk)

Else ' Между ними считаю по интерполяции полиномом с расположением заданного икса между двух пар точек

For i = 3 To Xt.Count - 1

If Xisk < Xt.Rows(i) Then

xd(1) = Xt.Rows(i - 2): xd(2) = Xt.Rows(i - 1): xd(3) = Xt.Rows(i): xd(4) = Xt.Rows(i + 1)

yd(1) = Yt.Rows(i - 2): yd(2) = Yt.Rows(i - 1): yd(3) = Yt.Rows(i): yd(4) = Yt.Rows(i + 1)

Linia_trenda yd, xd, 3, cd

kus_interp_Ex = cd(1) * Xisk ^ 3 + cd(2) * Xisk ^ 2 + cd(3) * Xisk ^ 1 + cd(4)

Exit For

End If

Next i

End If

End If

Case Else

End Select

End Function

======

Входные данные:

Xt - Столбец исходных Х

Yt  - Столбец исходных Y

Xisk  - X при котором требуется определить Y

toch - количество используемых точек при интерполяции.


Наблюдательный заметит, что в макросе присутствует и интерполяция с использованием 3-х точек. (0_о)


Дополнительная функция, требуемая для макроса кусочной интерполяции - определение коэффициентов полинома линии тренда:


======

' Проведение интерполяции с использованием функционала Excel

' На выходе - коэффициенты полинома. Число точек должно быть минимум на одну больше, чем степень полинома.

' Данные берутся из программы

Public Sub Linia_trenda(ByRef Y() As Double, ByRef x() As Double, ByVal PolyStep As Integer, ByRef c() As Double, Optional ByRef r2 As Double)

Dim stepen As Long

' Ввожу проверку не превышения степени массива

If (UBound(Y) - LBound(Y) - 1) < PolyStep Then

stepen = UBound(Y) - LBound(Y)

Else

stepen = PolyStep

End If

' Объявляю переменные, создаю матрицы под размер данных и степень полинома.

Dim X1() As Double, Y1() As Double

ReDim X1(LBound(Y) To UBound(Y), 1 To stepen) As Double

ReDim Y1(LBound(Y) To UBound(Y), 1 To 1) As Double

ReDim c(1 To stepen + 1) As Double

' Заполню массив Х в соответствии со степенью уравнения.

For i = LBound(x) To UBound(x)

Y1(i, 1) = Y(i)

X1(i, 1) = x(i)

For N = 2 To stepen

X1(i, N) = X1(i, 1) ^ N

Next N

Next i

' Нахожу уравнение.

Dim Coefs As Variant

Coefs = WorksheetFunction.LinEst(Y1, X1, True, True)

' Вытаскиваю коэффициенты полинома.

For i = 1 To stepen + 1

c(i) = Coefs(1, i)

Next i

' Вытаскиваю величину достоверности апроксимации.

r2 = Coefs(3, 1)

End Sub

======

Макрос linterp был представлен в прошлый раз.


Одним из замечательных применений кусочной интерполяции является возможность автоматического создания макросов функций без проблем с невозможностью достоверной апроксимации исходных данных вот в таком виде:


' Поправки Сербия Панчево Страница 39 из 77

Public Function ТЭХ_ПТ80_Рис3(ByRef Go As Single) As Variant

Dim Xt As Variant

Dim Yt As Variant

Xt = Array(13.0042194092827, 13.4767932489451, 14.0675105485232)

Yt = Array(-6.38888888888889E-02, -6.38888888888889E-02, -0.06875)

ТЭХ_ПТ80_Рис3 = kus_interp(Xt, Yt, Go, 4, 2)

End Function


Учтите что kus_interp при использовании данных с листа и из макроса отличаются...

Но об этом в следующий раз.


===========================

Планы на будущее

1. Часть 7. Автоматическое создание макроса функции с использованием кусочной интерполяции.

2. Построим поиск решения.

3. Строим график функции.

Показать полностью 5
161

Базы данных в Excel. Или ВПР по неограниченному количеству условий

Ответ на пост из соседней темы о ВПР по 2-м условиям.

Довольно часто я встречаю решения выполнения поиска в таблице по двум/трём условиям с применением ВПР/ГПР. Подчас решения очень интересные. Однако что ВПР, что ГПР выдают только одно значение, и решения не масштабируемы. А что делать если необходимо из массива выбрать всю строку, которая будет подчиняться набору критериев?

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

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

Ход поиска однотипен:

1. Столбцы исходной таблицы имеют уникальные заголовки.

2. Для выборки из данной таблицы создаём таблицу условий. При этом заголовок таблицы условий должен совпадать с названием столбца из исходной таблицы, по которому будет проходить выборка. Если выборка по одному столбцу, но по нескольким условиям, то можно написать всё в один столбец (см.скрин ниже). Если условия по нескольким столбцам - по одному условию на столбец, наименования столбцов могут повторяться.

3. Затем переходим в вкладку "Данные" - "Сортировка и фильтр" - "Дополнительно". В открывшемся окне

3.1. отмечаем "скопировать результат в новое место". В этом случае исходная таблица не изменится.

3.2. Исходный диапазон - таблица исходная, вместе с шапкой

3.3. Диапазон условий - таблица условий, вместе с шапкой

3.4. Адрес ячейки с которой будет заполняться итоговая таблица согласно выборки.

Итоговая таблица при этом никак не связана с исходной. И с ней можно проводить любые операции.

Базы данных в Excel. Или ВПР по неограниченному количеству условий Microsoft Excel, Vba, Плюшка, Длиннопост

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

=====

Sub Выборка()

Range("B3:B37").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("I3:I6"), _

CopyToRange:=Range("K3"), _

Unique:=False

End Sub

=====

где

Range("B3:B37") - исходная таблица

Range("I3:I6") - таблица критериев

Range("K3") - начало вывода результирующей таблицы


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


Вызов функции выборки:

AdvancedFilter (Действие, CriteriaRange, CopyToRange, Уникальный)

Базы данных в Excel. Или ВПР по неограниченному количеству условий Microsoft Excel, Vba, Плюшка, Длиннопост

Причём работа с таблицами как с базами на этом не ограничивается, в частности она позволяет выполнять быстрые расчёты с учётом критериев. Например находить минимальные, максимальные и средние значения только для строк удовлетворяющих критериям. Заметьте - проблемы с числом критериев нет.

Базы данных в Excel. Или ВПР по неограниченному количеству условий Microsoft Excel, Vba, Плюшка, Длиннопост

Например, если я хочу просуммировать все значения из столбца "Продажи" с учётом того, что они проводились в марте, и контактов было от 35...36, то я создаю таблицу критериев (см.скрин ниже) и в любой ячейке листа формулу  =БДСУММ(B6:G21;F6;B2:В3)

где

B6:G21 - Диапазон исходной таблицы, включая заголовок;

F6 - по какому столбцу буду суммировать;

B2:D3 - при каких условиях суммировать.

Базы данных в Excel. Или ВПР по неограниченному количеству условий Microsoft Excel, Vba, Плюшка, Длиннопост

Да, сейчас это можно заменить на СУММЕСЛИ, но если будет больше условий? А если суммируется при сложных условиях содержания ячеек?

Насколько способ с использованием баз нагляднее, не так ли?

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

Базы данных в Excel. Или ВПР по неограниченному количеству условий Microsoft Excel, Vba, Плюшка, Длиннопост

Ну и естественно умные таблицы используются без проблем

Базы данных в Excel. Или ВПР по неограниченному количеству условий Microsoft Excel, Vba, Плюшка, Длиннопост

==================


Рассмотрим ещё один метод  поиска значения по 2-м, 3-м и более критериям без применение ВПР.

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

Допустим есть таблица исходных данных (Таблица1), в корой представлены список  сотрудников (столбец Name) с датой их приёма на работу (Start) увольнения с должности (Stop) и названием должности (Role). При этом если человек принят, но ещё не уволился, то его ячейка Stop пустая. Нужно найти должность человека (F3) на какую то дату (F4). Попробуйте это через ВПР прописать ради интереса. А вот так делается без ВПР:

Базы данных в Excel. Или ВПР по неограниченному количеству условий Microsoft Excel, Vba, Плюшка, Длиннопост

В ячейке F6 собственно формула поиска.

=ЕСЛИОШИБКА(ПРОСМОТР(2;1/(Таблица1[Name]=F3)/(Таблица1[Start]<=F4)/((Таблица1[Stop]="")+(Таблица1[Stop]>=F4));Таблица1[Role]);"нет данных")


Разберём структуру поиска и задания условий:

Таблица1[Name]=F3 - совпадение имени

Таблица1[Start]<=F4 - дата зачисления на работу меньше даты поиска

(Таблица1[Stop]="")+(Таблица1[Stop]>=F4) - дата поиска или меньше даты увольнения или дата увольнения пустая.

Таблица1[Role] - вывод ячейки для которой все три условия истина.


Как видно формула легко масштабируется под любое количество условий.


Ну вот как то так.

Этим постом я хотел показать, что применение ВПР(ГПР) не всегда оправдано (хотя несомненно знание этих функций обязательно), и есть более простые и лёгкие способы.

Показать полностью 7
Отличная работа, все прочитано!