MS Excel - решение практических задач

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

------------------------------

Вопросы и ответы, а также советы и рекомендации по настройке MS Excel, ошибки в работе программы, решение задач по импорту/экспорту документов, ссылки и обсуждение надстроек для Excel, обучающие материалы и т.п. размещаем в теме MS Excel - настройка, импорт/экспорт.
Если решение задачи не требует применения формул и макросов (VBA), то это обсуждение будет перенесено в тему по настройкам.

Все пожелания по принципам размещения сообщений по этим темам рассматриваются - пишите в личку Andrey_k.

[votya]

Можно поподробней. Сервис\параметры\общие\параметры веб документа посмотрел, но ничего, что могло навести меня на ответ там не обнаружил. Мне тут знакомый говорил что без макроса, или как там его, здесь не обойтись. А я VBA вообще ни разу в глаза не видел...Вот как тут быть?

Добавлено через 38 минут
Все. Разобрался. Никакой макрос там не нужен. Там в Сервис\Параметры\Вычисления нужно было поставить галочку итерации и количество 1 поставить.

Добавлено через 47 минут
А никто не знает как то же самое в OpenOffice Calc сделать? Че то тоже самое там не работает...

[Olegich]

Здравствуйте! Помогите пожалуйста разобраться с электронной таблицей. Суть задачи такова: Допустим создаю свою базу сотовых номеров. Каждый номер пишеться в одном столбце, но в своей ячейке (А1, А2....и т.д.). Как мне сделать так, чтобы если номер (значение) повторяется в данном столбце, то программа сообщала мне об ошибке или выделяла другим цветом. Раньше я неплохо разбирался в составлении формул, но это 5 лет прошло (студенческие годы). Сейчас не помню. Помогите, кто знает как это сделать, пожалуйста!

[SV80]

Цитата: Сообщение от Avenger Ответ: формулу создать в экселе помоему невозможно, они существуют и их надо знать, если интересует тебя данная информация могу дать все форумулы какие есть в ПМ

Возможно – есть пользовательские функции – они пишутся в VBA. Меня не раз выручали. Посмотри в справке пользовательские функции.

Добавлено через 6 минут

Цитата: Сообщение от votya Как создать в excel рекурсивную функцию, ссылающуюся саму на себя - задача подсчет в ячейке суммы нарастающим итогом а1=а1+а2.

Если использовать сводную таблицу там есть сумма нарастающим итогом. Если не подходит добавь еще один столбец «B» например и напиши формулу в ячейке «B2» =СУММ($A$1:A2), скопируй ее в остальные ячейки внизу (или так сказать растяни)

Добавлено через 15 минут

Цитата: Сообщение от Olegich Здравствуйте! Помогите пожалуйста разобраться с электронной таблицей. Суть задачи такова: Допустим создаю свою базу сотовых номеров. Каждый номер пишеться в одном столбце, но в своей ячейке (А1, А2....и т.д.). Как мне сделать так, чтобы если номер (значение) повторяется в данном столбце, то программа сообщала мне об ошибке или выделяла другим цветом. Раньше я неплохо разбирался в составлении формул, но это 5 лет прошло (студенческие годы). Сейчас не помню. Помогите, кто знает как это сделать, пожалуйста!

Добавь еще один контрольный столбец «B» и напиши формулу в ячейке «B2» =ЕСЛИ(ЕНД(ПОИСКПОЗ(A2;A$1:A1;0));"Значение уникально";"Значение уже есть"), скопируй ее в остальные ячейки внизу (или так сказать растяни). Потом можем воспользоваться условным форматированием.

[volontoyr]

Люди, помогите пожалуйста. Как в экселе сделать чтоб набираемые цифры (сумма)в ячейки отображались прописью?
пример:
1237,00
отображалась бы
Одна тысяча двести тридцать семь рублей 00 копеек
А то которая функция имеется, то она доступна на малазийском языке или на кокам-то таком.
Иож у кого есть скрипт какой-нить, а то каждый раз прописывать большие суммы надоедает.

[Grifon]

В таблице разнотипные данные, текст (русский и англиЦкий), цыферы и всяческие символы (<, “, ; и т.д.). Формат всех ячеек текстовый. Сохраняю в текстовый документ - ‘Сохранить как’ если текст Юникод - русский читабельный, но расставляет кучи кавычек по непонятному принципу, если форматированный текст (разделители пробелы) в итоге вместо русского иероглифы, но нет кавычек.
Макросом также

Sub TXT()
ActiveWorkbook.SaveAs Filename:="C:\txt.txt", FileFormat:= _
xlUnicodeText, CreateBackup:=False
End Sub

НО! Есть параметр TextCodepage (по ходу не работает).

Помогите избавиться от кавычек и сохранить русскую кодировку.


З.Ы.
"Спасибо модератору"... Теперь я точно от кавычек не избавлюсь 8(

[kosLLL]

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

[Enfriz]

kosLLL, то есть отбор? чтобы данные подставлялись?
тогда

=ЕСЛИ(<условие>;<имя ячейки, из которой беруться данные>;<значение при невыполнениии условия>)

Например =ЕСЛИ(A1>1;A2;B1). Или это не то, что вам надо?

[SV80]

Цитата: Сообщение от Grifon В таблице разнотипные данные, текст (русский и англиЦкий), цыферы и всяческие символы (<, “, ; и т.д.). Формат всех ячеек текстовый. Сохраняю в текстовый документ - ‘Сохранить как’ если текст Юникод - русский читабельный, но расставляет кучи кавычек по непонятному принципу, если форматированный текст (разделители пробелы) в итоге вместо русского иероглифы, но нет кавычек. Макросом также Sub TXT() ActiveWorkbook.SaveAs Filename:="C:\txt.txt", FileFormat:= _ xlUnicodeText, CreateBackup:=False End Sub НО! Есть параметр TextCodepage (по ходу не работает). Помогите избавиться от кавычек и сохранить русскую кодировку. З.Ы. "Спасибо модератору"... Теперь я точно от кавычек не избавлюсь 8(

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

[roland76]

Цитата: Сообщение от SV80 Не понятна причина сохранения... Если скажешь может другие пути - более удобные подскажам Но сохраняя в текст, ексель как раз пользуется запятыми для разделения данных. Поэтому или уточняй задачу или пиши макрос сохранения в текстовый файл, где идет перебор диапазона ячеек, вытаскивание значения каждой ячейки и добавление значения в строку и запись всех строк в текстовый файл. А тот макрос что ты привел он просто делает теже действия что и ты через меню - только нажатием одной кнопки - так что иного результата зря ожидаешь...

Вот тебе макрос, который сохраняет sheet в csv файл. Откорректировав его, думаю с легкостью решишь свою задачу.

Sub SaveAsCSV()

Dim ws As Worksheet
Dim fname As String
Dim fs
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String
Dim Sep As String

Application.ScreenUpdating = False

'On Error GoTo EndMacro:

Set ws = ActiveSheet
FNum = FreeFile

Sep = ";"

fname = "C:\temp\cat.csv"

'Determine if the same CSV file aready exists 'If it does, DELETE it and create a new one from 'scratch Set fs = CreateObject("Scripting.FileSystemObject")
'If fs.FileExists(fname) Then
'Kill fname
'End If
   If FileExists(fname) Then
       Set fs = CreateObject("Scripting.FileSystemObject")
       fs.DeleteFile (fname)
   End If

ws.Range(Cells(1, 1), Cells(LastRow(ws), LastCol(ws))).Select

With Selection
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With

Open fname For Output Access Write As #FNum

For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = ""
Else
CellValue = (Cells(RowNdx, ColNdx).Value)
'Application.WorksheetFunction.Text _
'(Cells(RowNdx, ColNdx).Value, _
'Cells(RowNdx, ColNdx).NumberFormat)
End If
WholeLine = WholeLine & CellValue & Sep
Next ColNdx
WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
Print #FNum, WholeLine
Next RowNdx

EndMacro:
'On Error GoTo 0

Application.ScreenUpdating = True

Close #FNum

Range("A1").Select

End Sub

Добавлено через 1 минуту

Цитата: Сообщение от SV80 Не понятна причина сохранения... Если скажешь может другие пути - более удобные подскажам Но сохраняя в текст, ексель как раз пользуется запятыми для разделения данных. Поэтому или уточняй задачу или пиши макрос сохранения в текстовый файл, где идет перебор диапазона ячеек, вытаскивание значения каждой ячейки и добавление значения в строку и запись всех строк в текстовый файл. А тот макрос что ты привел он просто делает теже действия что и ты через меню - только нажатием одной кнопки - так что иного результата зря ожидаешь...

Вот тебе макрос, который сохраняет sheet в csv файл.

Откорректировав его, думаю с легкостью решишь свою задачу.

Sub SaveAsCSV()

Dim ws As Worksheet
Dim fname As String
Dim fs
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String
Dim Sep As String

Application.ScreenUpdating = False

'On Error GoTo EndMacro:

Set ws = ActiveSheet
FNum = FreeFile

Sep = ";"

fname = "C:\temp\cat.csv"

'Determine if the same CSV file aready exists 'If it does, DELETE it and create a new one from 'scratch Set fs = CreateObject("Scripting.FileSystemObject")
'If fs.FileExists(fname) Then
'Kill fname
'End If
   If FileExists(fname) Then
       Set fs = CreateObject("Scripting.FileSystemObject")
       fs.DeleteFile (fname)
   End If

ws.Range(Cells(1, 1), Cells(LastRow(ws), LastCol(ws))).Select

With Selection
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With

Open fname For Output Access Write As #FNum

For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = ""
Else
CellValue = (Cells(RowNdx, ColNdx).Value)
'Application.WorksheetFunction.Text _
'(Cells(RowNdx, ColNdx).Value, _
'Cells(RowNdx, ColNdx).NumberFormat)
End If
WholeLine = WholeLine & CellValue & Sep
Next ColNdx
WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
Print #FNum, WholeLine
Next RowNdx

EndMacro:
'On Error GoTo 0

Application.ScreenUpdating = True

Close #FNum

Range("A1").Select

End Sub

[diuk]

Цитата: Сообщение от volontoyr Люди, помогите пожалуйста. Как в экселе сделать чтоб набираемые цифры (сумма)в ячейки отображались прописью? пример: 1237,00 отображалась бы Одна тысяча двести тридцать семь рублей 00 копеек А то которая функция имеется, то она доступна на малазийском языке или на кокам-то таком. Иож у кого есть скрипт какой-нить, а то каждый раз прописывать большие суммы надоедает.

закачай надстройку и пользуйся _http://softsearch.ru/programs/51-504-summa-propis-ju-download.shtml

Добавлено через 6 минут

Цитата: Сообщение от Alexis78 Потому что в шаблонах нет "пустого" шаблона, типа "книга".xls

не пойму зачем? но если нужно, сохрани пустую книгу как шаблон....

[plk_ua]

2 volontoyr
Люди, помогите пожалуйста. Как в экселе сделать чтоб набираемые цифры (сумма)в ячейки отображались прописью?
пример:
1237,00
отображалась бы
Одна тысяча двести тридцать семь рублей 00 копеек

Есть такая штука - UDF.XLS называется - во всяком случае я ее под таким именем нашел сто лет назад.
Не найдешь - стучи.

[Nerses]

Цитата: Сообщение от volontoyr Люди, помогите пожалуйста. Как в экселе сделать чтоб набираемые цифры (сумма)в ячейки отображались прописью? пример: 1237,00 отображалась бы Одна тысяча двести тридцать семь рублей 00 копеек А то которая функция имеется, то она доступна на малазийском языке или на кокам-то таком. Иож у кого есть скрипт какой-нить, а то каждый раз прописывать большие суммы надоедает.

Нашел интересную статью на эту тему, хотя сам не пробовал.
http://msexcel.ru/content/view/97/2/
http://office.microsoft.com/ru-ru/wo...862221049.aspx

[Tin]

Используя функцию СЧЁТЕСЛИ при совпадении чисел в столбце,
прога считает за одно. Как исправить?
Пример: 1, 2, 3, 4, 4, 5

=СЧЁТЕСЛИ(A1:A6;">3")
Должно получиться 3, но пишет 2, т.е. (4) считает за один.

2007 EX

[valeransk1]

Цитата: Сообщение от Tin Используя функцию СЧЁТЕСЛИ при совпадении чисел в столбце, прога считает за одно. Как исправить? Пример: 1, 2, 3, 4, 4, 5 =СЧЁТЕСЛИ(A1:A6;">3") Должно получиться 3, но пишет 2, т.е. (4) считает за один. 2007 EX

встроенными средствами экселя никак. в visual basic который встроен это можно сделать. при помощи массивоф

[Tin]

valeransk1,

Цитата: встроенными средствами экселя никак. в visual basic который встроен это можно сделать. при помощи массивоф

Не подскажете, поступенчато, как это сделать?
Или где почитать про это.