Hi
I’d been working with AS for a couple of weeks. I was looking for a way to do in Numbers what Excel does with the “Data/Table” function, that is to know how a result or function depends on certain parameters. How sensitive it is to each one (a sensitivity analysis http://en.wikipedia.org/wiki/Sensitivity_analysis) You start with a model of something -in my example a store- where you have a result (Revenues, Tax, ) that is a function of 2 (or more) parameters (Price, Quantity, Fix Cost…).
You are supposed to start the script from the sheet you are working on. It requires to have 2 (or more) tables. One with the model parameters and results, formulas, etc. The second contains only the table with a reference to the result we want to analyze in cell A1. Something like “=Tabla Parametros :: C15” where “Tabla Parametros” is the first table. In the rest of column “A” (“A2:Ann”) must be the values we want the first parameter (I called it X) to take. In the rest of row 1 ((“B1:zz1”) the values for the second parameter (Y). The space inside row 1 and column A is reserved to the results of the function.
When you run the script you are prompted to select:
1-. the Table with the model,
2-. the X parameter
3-. the Y parameter
4-. the second table where it is going to display the results.
The script is very basic, with no error handling, and would like to improve the dialogs, and other capabilities.
Here it is
on buscar(docu)
tell application "Numbers" to tell docu
set HojaActiva to first item of every sheet
repeat with Hojax in every sheet
tell Hojax
set TablaActiva to item 1 of every table
repeat with Tablax in every table
if exists selection range of Tablax then
set TablaActiva to Tablax
set HojaActiva to Hojax
end if
end repeat
end tell
end repeat
return {TablaActiva, HojaActiva}
end tell
end buscar
on SeleccioneRango(Documento, Hoja, Tabla, Mensaje)
tell application "Numbers" to tell Documento to tell Hoja to tell Tabla
activate "Numbers"
set selection range to range "A1"
set ActualRange to name of selection range
display dialog Mensaje with title "OK & Select" (*giving up after 1*)
set waiting to true
repeat while waiting
if selection range's name is not ActualRange then set waiting to false
end repeat
set NombreRango to name of selection range
tell AppleScript to set text item delimiters to ":"
set NomCelda to first text item of NombreRango
set NomCeldaX to second text item of NombreRango
set Celda to cell NomCelda
set rango to range NombreRango
if NomCelda is equal to NomCeldaX then
return Celda
else
return NombreRango
end if
end tell
end SeleccioneRango
-----aqui comienza
tell application "Numbers"
set DocumentoActivo to document 1
end tell
set Ubicacion to buscar(DocumentoActivo)
set HojaActiva to second item of Ubicacion
set Tabla to first item of Ubicacion
set nombreTabla to name of first item of Ubicacion
tell application "Numbers"
tell DocumentoActivo
tell HojaActiva
set TablasDisponibles to {}
repeat with Tablax in every table
copy name of Tablax to end of TablasDisponibles
end repeat
set NombreTablaActiva to (choose from list TablasDisponibles with title "Select & OK" with prompt "Select Model Table" without multiple selections allowed) as text
set TablaActiva to table NombreTablaActiva
end tell
end tell
end tell
set Texto to "Select Vertical Variable Parameter (X) "
set CellA to SeleccioneRango(DocumentoActivo, HojaActiva, TablaActiva, Texto)
set Texto to "Select Horizontal Variable Parameter (Y)"
set CellB to SeleccioneRango(DocumentoActivo, HojaActiva, TablaActiva, Texto)
tell application "Numbers" to tell DocumentoActivo to tell HojaActiva
repeat with Tablax in TablasDisponibles
if Tablax is not equal to NombreTablaActiva then set DefaultTable to Tablax
end repeat
set NombreResultados to (choose from list TablasDisponibles with title "Select & OK" with prompt "Select Results Table" default items DefaultTable without multiple selections allowed) as text
set TablaResultados to table NombreResultados
tell TablaResultados to set Funcion to cell 1 of row 1
set CeldasX to count cells of column "A" of TablaResultados
set CeldasY to count cells of row 1 of TablaResultados
set i to 1
set j to 1
tell TablaResultados
repeat with j from 2 to CeldasX
tell column "A"
set value of CellA to value of cell j
end tell
repeat with i from 2 to CeldasY
tell row 1 to set value of CellB to value of cell i
tell row j to set value of cell i to value of Funcion
end repeat
end repeat
end tell
end tell
I’d enjoy a lot learning this. I get a lot of information from this and other forums. So thanks.