Пересечение столбцов и строк (индекс, поискпоз)

Автор Rita, 19.03.2014, 11:50

« назад - далее »

Rita

Всем доброго дня!
Помогите, пожалуйста, решить эту простую задачку.
Есть матрица тарифов по маршрутам. Есть таблица, в уоторой расписаны выполненные маршруты. Необходимо, чтобы при поездке, например, из Москвы в Эстонию и обратно (которая выводится двумя строками) в таблицу выполненных маршрутов подтягивался тариф на пересечении позиции "Москва" столбца российских городов и позиции "Эстония" иностранных тариф по маршруту Москва-Эстония, а в сл.строке - тариф по маршруту Эстония - Москва (см.пример).
Проблема в том, что я не могу прописать формулу на обратный маршрут в одну формулу. Пыталась черз "ИЛИ", не получается.

cheshiki1

#1
=ЕСЛИ(ЕНД(ПОИСКПОЗ(D3;Тарифы!$A$1:$D$1;0));ГПР(C3;Тарифы!$B$1:$D$3;ПОИСКПОЗ(D3;Тарифы!$A$1:$A$3;0););ВПР(C3;Тарифы!$A$2:$D$3;ПОИСКПОЗ(D3;Тарифы!$A$1:$D$1;0);))
или
=ЕСЛИОШИБКА(ГПР(C3;Тарифы!$B$1:$D$3;ПОИСКПОЗ(D3;Тарифы!$A$1:$A$3;0););ВПР(C3;Тарифы!$A$2:$D$3;ПОИСКПОЗ(D3;Тарифы!$A$1:$D$1;0);))

а еще лучше сделать нормальную таблицу тарифов и не нужно будет длинных формул.

Czeslaw


Rita

Цитата: Czeslaw от 19.03.2014, 12:32
Как Вам такой вариант?

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

Rita

Цитата: cheshiki1 от 19.03.2014, 12:26
=ЕСЛИ(ЕНД(ПОИСКПОЗ(D3;Тарифы!$A$1:$D$1;0));ГПР(C3;Тарифы!$B$1:$D$3;ПОИСКПОЗ(D3;Тарифы!$A$1:$A$3;0););ВПР(C3;Тарифы!$A$2:$D$3;ПОИСКПОЗ(D3;Тарифы!$A$1:$D$1;0);))
или
=ЕСЛИОШИБКА(ГПР(C3;Тарифы!$B$1:$D$3;ПОИСКПОЗ(D3;Тарифы!$A$1:$A$3;0););ВПР(C3;Тарифы!$A$2:$D$3;ПОИСКПОЗ(D3;Тарифы!$A$1:$D$1;0);))

а еще лучше сделать нормальную таблицу тарифов и не нужно будет длинных формул.

Спасибо Вам огромное! В примере все работает. Буду пробовать на реальной базе.  ;)

Rita

Уважаемый cheshiki1!
Подскажите, что Вы подразумеваете под "Нормальной базой тарифов"? Может мне и правда стоит что-то поменять.

cheshiki1

Цитата: Rita от 19.03.2014, 12:44
Уважаемый cheshiki1!
Подскажите, что Вы подразумеваете под "Нормальной базой тарифов"? Может мне и правда стоит что-то поменять.
У Czeslaw таблица такого вида.
значения вводить дважды не нужно, в верхней правой части значения, в нижней левой формула если значения 100% одинаковые (формула ТРАНСП вводится на весь диапазон как формула массива).

Rita

Цитата: cheshiki1 от 19.03.2014, 13:32
Цитата: Rita от 19.03.2014, 12:44
Уважаемый cheshiki1!
Подскажите, что Вы подразумеваете под "Нормальной базой тарифов"? Может мне и правда стоит что-то поменять.
У Czeslaw таблица такого вида.
значения вводить дважды не нужно, в верхней правой части значения, в нижней левой формула если значения 100% одинаковые (формула ТРАНСП вводится на весь диапазон как формула массива).

Тогда у меня второй вопрос. Я не умею так составлять таблицу автоматически.  ::)
Сейчас матрица примерно 50*50 (строк и столбцов соответственно). Как ее транспонировать (или что-то еще с ней сделать), чтобы она симметрично откопировалась?  :D
+ после того, как она примет нужный вид, мне, например, надо поменять в ней 100 тарифов. Мне придется каждый из этих 100 менять и в зеркальной части этой матрицы? Можете немного поподробнее написать, а то я не настолько сильна в этой области.  :P

cheshiki1


Rita

Цитата: cheshiki1 от 19.03.2014, 15:34
покажите начальную матрицу.

Во вложении. Я уже вставила туда Вашу формулу. В таком варианте все работает.

Ktulu


Rita

Цитата: Ktulu от 20.03.2014, 04:55
Так?

Да!!! Спасибо! Именно так!  :)
А может у Вас еще и макрос есть на транспонирование?   8)

cheshiki1

скажем так одноразовый макрос, т.е. переделать таблицу текущую, после переделки он уже не нужен т.к. будет работать не так как нужно.
Sub Переделка1()
Dim i&, H&, j&
Application.ScreenUpdating = False
Range(Cells(2, 3), Cells(2, Cells(Rows.Count, 2).End(xlUp).Row)).EntireColumn.Insert
Range(Cells(3, 2), Cells(3, 2).End(xlDown)).Copy
Range("C2").PasteSpecial Paste:=xlPasteAll, Transpose:=True
Range(Cells(2, 3), Cells(2, 3).End(xlToRight)).Copy
Range("B3").PasteSpecial Paste:=xlPasteAll, Transpose:=True
H = Cells(Rows.Count, 2).End(xlUp).Row
For i = 3 To H
   For j = i To H
   Cells(j, i).FormulaLocal = "=" & Cells(i, j).Address & ""
   Next j
Next
With Range(Cells(2, 2), Cells(H, H))
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Application.CutCopyMode = False
Range("B3").Select
Application.ScreenUpdating = True
End Sub

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

Rita

Что получилось - во вложении.
Файл ругается на циклическую ссылку  :D + постоянно подвисает из-за этого.
Макрос выполнялся 6 минут, при этом у меня завис даже Outlook  :)
Но все равно спасибо.

cheshiki1

ну да цикл. ссылка имеет место
For j = i To H
нужно заменить на
For j = i + 1 To H

я не большой знаток VBA так что остальные вопросы имеют место быть. :(