Здравствуйте!

Скрытый текст

Скрытый текст Скрытый текст Имеется список уникальных (неповторяющихся) телефонных номеров, на которые происходили вызовы от абонентов телефонной станции, где я работаю специалистом по учёту трафика. Для того, чтобы определить местоположение вызываемого номера (страна, область, город), а также стоимость вызова, имеется таблица, в которой для каждого региона прописан префикс телефонных номеров, однозначно определяющий, какой стране, области или городу принадлежит набранный номер. Префикс состоит из нескольких цифр (для международных звонков на первой позиции идут цифры "10", затем код страны, например, префикс Украины будет "10380", Беларуси - "10375", а с учетом региона и/или оператора связи к этим цифрам добавится ещё несколько: Скрытый текст "10380443", "10380444", "10380445" - "Украина, Киев", ... "1038063" - "Украина, Mob, Astelit" и так далее. Скрытый текст Отсюда понятно, что набранные номера, начинающиеся с этих цифр, относятся к принадлежащему этому префиксу региону. Максимальное количество символов (цифр) в префиксе - 10, минимальное - 2. Моя задача - найти для каждого номера соответствующий ему регион и посчитать стоимость вызова. До сих пор я решал эту задачу на листе Excel с помощью формул: либо "многоэтажная" ЕСЛИОШИБКА-ВПР, которая последовательно ищет в таблице префиксов сначала по первым десяти символам номера, затем, если не найдено - по девяти и так до первых двух цифр, пока не найдет подходящий префикс; и любезно написанная для меня участником форума PlanetaExcel уважаемым R Dmitry массивная UDF, которая выполняет ту же задачу - ищет подходящий префикс (или соответствующее ему значение из таблицы префиксов). Поскольку обрабатываемых данных по телефонному трафику довольно много, производить расчёты на листах Excel путем многократных копирований и вставок больших массивов данных да ещё с десятками тысяч формул стало очень обременительно и чревато ошибками при расчётах. Обратил внимание на новые революционные методы работы с большими данными в Excel - Power Query и PowerPivot. К сегодняшнему дню большую часть работы переложил на эти продвинутые инструменты, но вышеописанную задачу пока решаю по старинке - формулами, а затем полученный результат отправляю в модель данных PowerPivot. Тут как раз проявляется недостаток формул - либо очень тяжёлая ВПР, либо несовместимая с работой в "умных" таблицах "массивная" UDF. Хотелось бы решать эту задачу средствами Power Query. Пробовал сам - создавал дополнительные столбцы к таблице с номерами: 10 первых символов, 9, 8 и тд. Затем по этим столбцам поэтапно через объединение запросов с таблицей префиксов создавал новые таблицы, потом объединял их, но это было очень ресурсоёмко и долго. К тому же, не исключались ситуации, когда к уже найденному префиксу с большей длиной, например, для номера 10(380)443-123-4567 найдено [10380443 - "Украина, Киев"], на следующих этапах объединения таблиц добавлялся префикс [10380 - Украина], который формально тоже удовлетворяет условиям отбора, но не должен попадать в результат, поскольку уже найден наиболее подходящий префикс. Вторая сложность - в таблице префиксов есть столбец Дата. В течении года тарифы на отдельные направления могут меняться, я решил не работать с разными таблицами префиксов в зависимости от даты вызова, а объединить все таблицы префиксов с разными датами в одну таблицу и при расчёте стоимости учитывать дату звонка, сопоставляя её с датой тарифа. Эту проблему я уже решил в PowerPivot, через составное ключевое поле [Prefix&"|"Date"]. Поэтому пока прошу помощи в Power Query - или написать функцию поиска префикса для номера, или другим способом, чтобы избавиться от постоянного прописывания тяжёлых формул в листах Excel.

Не знаком с расценками на подобные работы, хотелось бы уложиться в 1 тысячу, максимум - 1 500 рублей.
С уважением, Виктор Косенков.
Московская область, город Электросталь [телефоны удалены]

Файл с заданием большой, 1,2 Мб, поэтому ссылка на Яндекс-Диск: https://yadi.sk/i/52q_q76y3Q49pk

Добрый день!

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

Скрытый текст

Макрос, который скрывает листы, в зависимости от значения в заданной ячейке - (20 Апр 2017 12:06:17) Выделить имя листа из адреса ясейки/диапазона VBA - (19 Апр 2017 18:14:58) Настройка сводной таблицы - (19 Апр 2017 20:57:06) Создание сводной таблицы (VBA) - (20 Апр 2017 09:20:04) Как подключиться к MySQL по сети - (22 Апр 2017 20:26:44) закрепление ячеек за "группой" - (19 Апр 2017 17:52:55) Ошибка:Недостаточно ресурсов. Выберите меньше данных или закройте другие приложения. - (21 Апр 2017 23:17:38) Подсчет суммы выделенных объектов в ячейку справа - (19 Апр 2017 16:46:33) Копирование определенных (с заливкой) ячеек на другой лист - (21 Апр 2017 02:09:51) Ячейки не блокируются при использовании опции "защитить лист" - (19 Апр 2017 16:26:35)

А на форуме "Вопросы по Microsoft Excel" 13 последних тем от сегодняшнего числа, их не видно на главной:

Скрытый текст

Фиксация даты на изменения другой ячейки, защита данных ячейки от изменений следующим днем Печать листов Открытие файла из listbox Выборка данных по трем условиям и группировка в две колонки по заданным критериям Не работает функция СУММ Перенос скопированных данных на скрытый лист Извлечение слов из текста Как получить средствами VBA из ячейки адреса других ячеек (букву столбца или (и) номер строки), которые заявлены в формуле как аргументы Консолидация данных без группировки данных, 1 в 1 с источниками Поиск реализованного примера базы данных в книге Excel + формы навигации по ней Вложенные условия "Заморозка и разморозка" переменных данных в таблице Штрих код / QR код Сравнение данных

PS Чистил кэш, историю, куки. Безрезультатно.
Такая же картина на других компьютерах и даже со смартфона - при входе на сайт новые темы отображаются корректно, но стоит только залогиниться, и всё становится, как описал выше. Видимо, что то в моём профиле на сайте подвисло.
PPS А сегодня - 1 мая, всё исправилось, на главной - новые темы.

Здравствуйте!
Пытаюсь освоить надстройки Poverpivot и Power Query.
Как раз подвернулась задача, которую ранее решал через функцию Excel ВПР().
В таблице требовалось найти нужный тариф телефонного разговора.
Сложность в том, что тариф зависит не только от номера телефона, но и от даты звонка, так как оператор связи может менять тарифы несколько раз в месяц.
Поэтому таблица с тарифами состоит из строк с префиксами (первые цифры телефонного номера) и столбцов с датами изменения тарифов.
В PowerPivot связал таблицу с трафиком за месяц с таблицей с тарифами, но найти простое решение поиска нужного тарифа через функцию RELATED() не получилось.

Более подробное описание в файле, там же мои попытки разными способами найти решение.
Хотелось бы решение именно через PowerPivot/Power Query, так как объём реальных данных большой, а планирую в дальнейшем накапливать ежемесячные порции трафика в течение года для анализа и отчётов, что потребует больших объёмов памяти. Пишут, что для Power - надстроек Excel большие данные не представляют трудности.

Здравствуйте, Николай!
Сегодня заметил небольшую ошибку в работе инструмента "Изменение размеров диапазона".
При выборе в поле "Писать в ячейки" - если выбрать "слева направо по", то результат выводится сверху вниз,
и наоборот, при выборе "сверху вниз" результат выводится слева направо.
PS В Cправке по PLEX.pdf как раз после описания этого инструмента на 21 странице внизу ошибочно оказался абзац с заголовком "Зачем мне это может быть нужно?" с текстом, относящимся к описанию Микрографиков. Скорее всего, его место на 20 странице.

Здравствуйте, Николай!
Функция Coincidence() (степень подобия двух текстовых строк) иногда выдает неверный результат.
Например: - результат 0 - результат 1
хотя в обоих словах имеется сочетание "те", то есть правильный результат должен быть 2

Здравствуйте, уважаемые обитатели планеты Excel!
А вы в курсе, что сегодня - 27 декабря 2014 года - круглая дата!
В Excel, как известно, дата представляет собой порядковое число или количество дней, начиная с 1 января 1900 года.
Так вот сегодня это число равно 42000. Мало того, оно ещё и на 7 делится с круглым результатом - 6000, то есть сегодня не только сорокадвухтысячный день от летоисчисления Excel, но и шеститысячная суббота, такое не каждый год случается. В прошлый раз подобное совпадение "круглых" дней и недель (35000 и 5000) было 7000 дней тому назад - 28.10.1995 г.
Вряд ли это событие останется замеченным даже среди активных пользователей Excel, ведь дату мы видим именно как дату и редко обращаем внимание на то, что в числовом представлении 27.12.2014 выглядит вполне празднично, особенно накануне Нового Года  

Здравствуйте, уважаемые форумчане!
Вопрос скорее теоретический, чем практический. В реале проблема возникла при создании большой мегаформулы, но локализация проблемы привела к вполне простому и понятному на первый взгляд фрагменту.
Короче, имеем диапазон ячеек A1:C1 , заполненный любыми числами.
Формула, которая вытягивает эти числа в диапазон A6:A8

В ячейке A6 СТРОКА()-6 даёт нулевое смещение по столбцам для ячейки $A$1 и возвращает число из A1,
в ячейке A7 СТРОКА()-6 даст смещение на один столбец вправо от $A$1 и возвращает число из  B1 и т.д.
Вроде бы всё правильно, но формула выдаёт ошибку #ЗНАЧ! с пояснением "Значение, используемое в формуле, имеет неправильный тип данных". Поиск по форуму выдал множество рабочих формул с одновременным использованием функций СУММПРОИЗВ, СМЕЩ и СТРОКА, а в этом простейшем случае почему-то получается ошибка?

Заглянул сейчас в свой профиль и вижу, что дата рождения - 5 мая (на самом деле - 28 мая).
Захожу в редактирование профиля, а там всё правильно - 28. На всякий случай ещё раз ввожу правильную информацию, подтверждаю изменение профиля, но в профиле продолжает отображаться неверная информация - 5 мая.

Добрый день, уважаемые знатоки Excel!  
Жизнь заставила заняться биллингом - учётом стоимости телефонных разговоров.    
Специалист, работавший до меня, уволился. С профессиональной программой для биллинга предстоит знакомиться и разбираться, а пока решил часть задач решать с помощью Excel.  
Задача описана в прилагаемом файле, основная проблема в большом количестве вспомогательных формул и столбцов, из за чего Excel сильно тормозит.  
Просьба помочь советом, как можно оптимизировать работу формул / отказаться от дополнительных столбцов или лучше использовать для этой задачи VBA?

Здравствуйте, уважаемые планетяне!  
Пришла мне в голову идея сделать головоломку в Excel.  
Имеется "квадрат" 8х8 ячеек, в котором ячейки раскрашены в 8 цветов. (Вообще размер "квадрата" не обязательно должен быть 8х8, просто для примера взял именно такой). Изначальное расположение цветов либо по строкам (например - первая строка красная, вторая - жёлтая и т.д.), либо по столбцам, суть "головоломки" - перемешать закрашенные ячейки, а затем собрать изначальное расположение, то есть привести к порядку (что-то вроде кубика-Рубика).  
Ячейки должны перемещаться циклически в каждой строке/столбце, то есть сдвигаться влево, вправо, вверх, вниз, при этом последняя ячейка становится первой при движении вправо или первая становится последней при движении влево. Двигается строка/столбец с текущей ячейкой. Аналогично происходит передвижение вверх/вниз.    
Вернее, не происходит, а должно происходить, так как код VBA для "влево" и "вправо" у меня есть и я его привязал к кнопкам, а вот написать код для вертикальных перемещений у меня ума не хватает :-(  
Если знатоков VBA не затруднит, помогите пожалуйста довести задумку "до ума".  
 
PS Раскраску ячеек реализовал через УФ, так как алгоритм сдвига был изначально предназначен для содержимого ячеек, а как сдвигать формат ячеек в VBA, я не знаю. Из за 8 условий УФ формат файла - xlsm, в xls столько условий УФ не поддерживается.  
 
С уважением, Виктор.

Подскажите как к ячейке добавить примечание в виде картинки